Continuing on from last week’s EDATE function, today we’re looking at the Excel EOMONTH function.
If you use Excel to generate invoices and your payments are due at the end of the month you can use the EOMONTH function to automatically enter the payment due date.
Excel EOMONTH Function
=EOMONTH(start_date,months)
Returns the serial number of the last day of the month before or after a specified number of months.
Excel EOMONTH Function Examples
Note: only whole numbers are recognised by the 'months' argument. e.g. 1.7 months would be rounded down to 1.
Tip 1: EOMONTH works with dates stored as text (e.g. cell B14 in the image above), but this is not reliable. It's always best to work with date serial numbers.
Tip 2: There's no 'STARTOFMONTH' function, but you can also use EOMONTH to find the start of a month, e.g. let's say your 'start date' is 30th January and you want to return 1st January. You simply use EOMONTH to find the end of the previous month and then add 1:
Formula: =EOMONTH(DATE(2017,01,30), -1) +1
Result: = 1st January 2017
Tip 3: Calculate End of Month Date from Today
=EOMONTH(NOW(),0)
Use this on invoice templates where invoices are due at the end of the month.
Or if your credit terms are EOM+30 days then use:
=EOMONTH(NOW(),1)
Note: if the date displays as a serial number simply change the formatting to a Date format. CTRL+1 to open formatting dialog box.
More on working with date and time in Excel
Free eBook - Working with Date & Time in Excel
Everything you need to know about Date and Time in Excel - Download the free eBook and Excel file with detailed instructions.
Enter your email address below to download the comprehensive Excel workbook and PDF.
Please I need in urdu
Sorry we don’t have a translation
Hi Mynda,
In a related post (Excel Formula to Spread Income or Costs Over Months, I was going through your posts :-)) you use EOMONTH also. That reminded me of a inconcequence in this function: it is not really the end of month. You miss a complete day. Is this a known issue?
I discovered this by accident when reporting incidents. These incidents were tracked by the system in a date – time stamp. I noticed a certain difference between my report and the data from the system. I then formatted EOMONTH date in a format, and the end date was 2015-07-31 0:00:00, where you would expect 2015-07-31 23:59:59. So I ended up by adding 0,999994 to the EOMONTH date… Or of course add 1 to the dat, and don’t include this in your formula, like …<EOMONTH(,0)+1 in stead of <=EOMONTH
René
Hi René,
Great to see you’re continuing your Excel education 🙂
The EOMONTH function can cause a day loss if you then use it in a calculation, however it depends on your need for it. If you wanted to return the actual date of the end of the month then it does just that. However, if you want to subract this date from another and count the last day of the month then you need to adjust for that day.
This ‘inconsequence’ is apparent in any date calculation.
Kind regards,
Mynda
Hi Mynda,
Education in Excel is an on-going process. There are times I think I have reasonable knowledge, but then I come across a solution so clever I never could have thought of myself. That puts me back on the ground, which is a good thing :-).
Anyway, I like to improve, and reconsider my solutions for better ones.
As for date calculations, most of the time I am happy with just the date. But I would expect that EOMONTH really gives the (end of) last day, not the beginning of the last day. It is just good to know it is not, for cases where it really matters.
Another solution could be replacing the date+time formatted date with the integer of the date.
Cheers, René
“Education in Excel is an on-going process.”
Sure is. That’s one of the things I like about it.
I have tried the suggestion above, eomonth(now(),1) but it is not quite giving me what I need.
I want to know the due date of a invoice based on the date the invoice was raised. If this is a historical date, the ‘now’ option does not work. I need something like eomonth(‘invoice date’ + 30 days). Any ideas?
Thanks
Hi Nik,
Replace NOW() with your invoice date. e.g.
=EOMONTH(invoice date, 0)+30
Kind regards,
Mynda
That has fixed it. Many thanks, Mynda. 🙂
Awesome!
Like this site, it is very informative.
Is there a year function, as I need to reset counter to 001 at the change of every year.
Hi Oliveiro,
Yes, there is a YEAR() Function, which returns the year of the cell indicated in function argument.
Please upload a sample of your calculations to help us understand what are you trying to achieve, what counter are you trying to reset, as EOMONTH is showing the date from the end of the month from the indicated cell?
Use our Help Desk to send us a sample file, i will gladly help you.
Cheers,
Catalin