When using the EOMONTH function in Excel, you might encounter problems where the function doesn’t seem to work as expected. This can happen for several reasons, often related to how Excel interprets dates.
Table of contents
YouTube: Excel EOMONTH function not working.
Invalid Date Formats on entry
One reason why the EOMONTH function might fail is due to invalid dates (not the format, but Excel does not believe what you have given it is actually a date). As per the example below, if you have a column of dates and get a value error, it could be because Excel is not recognizing them as valid dates. To check this, highlight the cells and press Ctrl + 1 to bring up the Format Cells dialog box. If the sample value in the format preview doesn’t change when you switch between date formats or change it to a number, Excel isn’t recognizing your data as dates. Make sure your dates are in a format Excel can work with (see how to fix dates in Excel)
Formatting dates as numbers
Another common issue is when the EOMONTH function returns a number instead of a date. For example, you may see numbers like 37256 instead of a proper date. This happens when the date has been formatted as a number. Importantly, this is actual great because it means Excel definitely sees the date, you have just formatted it incorrectly. As shown below, to correct this, highlight the cells, press Ctrl + 1, and change the format to the date format you want.
Once this is done, Excel will properly display the date, as shown in the image below.
Incorrect Year Formatting
Sometimes, the EOMONTH function can produce unexpected results due to the way years and months are entered. For instance, you might see that the function is returning results with incorrect years (treating the year as a month or day for example). This often occurs when Excel misinterprets years entered with only two digits instead of the correct four digits. When formatting your dates, avoid using two-digit years to prevent confusion. To check or fix this, format the dates in a way that you can easily recognize which number is the year, as seen in the images below.
Handling Long Numbers as Dates
In some cases, you might have ‘dates’ represented as long numbers, which Excel cannot process correctly. For example, you might have data that reads as “01092023” instead of “1st September 2023”. Excel doesn’t understand this format as a date, so you’ll need to adjust it. The easiest fix is to correct the data so that Excel sees it as a date first ( see how to fix dates in Excel).
Tips and Troubleshooting:
- Always ensure that Excel recognizes your data as valid dates by using the Ctrl + 1 format option to check. Change the date to a number. If it changes it is a valid date to Excel. If not you need to fix it (see how to fix dates in Excel).
- If EOMONTH returns a number, reformat the cells using the date format in the Ctrl + 1 menu.
- Avoid using two-digit years to prevent Excel from misinterpreting your data.