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
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
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:
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.