Month ends with EOMONTH

1 / 4

Each invoice is due at the end of the next month, so the due date depends on how long the month is.

keyboard Typing formulas is easiest on a computer with a keyboard.

invoice dates

due dates

Type to enter a value or a formula starting with =. F2 edits, F4 adds $ to a reference.

Study notes

How EOMONTH works

What does EOMONTH do?

It gives the last day of a month: "end of month". You give it a date and how many months to move:
=EOMONTH(A2,0)
What does the second part mean?
- 0: the end of the same month. 10 Mar becomes 31 Mar.
- 1: the end of the next month. 10 Mar becomes 30 Apr.
- -1: the end of the month before. 10 Mar becomes 28 Feb (or 29 in a leap year).
Why not add 30 days?

Months have 28 to 31 days, so +30 lands on a different day each time. EOMONTH always lands on the real last day, February and leap years included.
How do I get the first day of a month?

Go to the end of the month before and add 1: =EOMONTH(A2,-1)+1 is the 1st of A2's month.
What about the same day next month?

That's EDATE: =EDATE(A2,1) turns 15 Mar into 15 Apr. It's what you want for renewal dates and monthly payments. Both give a date serial, so format the cell as a date.

Know someone fighting with a spreadsheet? Send them this page.