EDATE and EOMONTH practice exercises

Move a date by whole months, and find the last or first day of a month, for contract ends, renewals and payment terms.

Start the first exercise

How EDATE and EOMONTH work

EDATE moves a date forward or back by a whole number of months. EOMONTH finds the last day of a month, a number of months away. Both take two arguments: a start date and the number of months, where a negative number goes back in time.

=EDATE(A2,B2) adds the months in B2 to the start date in A2, so Jan 15, 2024 plus 6 months gives Jul 15, 2024. =EOMONTH(A2,0) returns the last day of the month the date is in: for Feb 10, 2024 that's Feb 29, 2024. Add 1 and =EOMONTH(A2,0)+1 is the first day of the next month. =DAY(EOMONTH(A2,0)) gives the number of days in the month.

Both functions return a day number, so format the result cell as a date. If a month is too short, EDATE lands on its last day: Jan 31 plus 1 month gives Feb 29 in 2024. The exercises below cover a contract end date, the last day of a month, the first day of next month and the days in a month.

  1. 1Contract end date with EDATEIn C2, use EDATE to find the date the number of months in B2 after the start date in A2.
  2. 2Last day of the month with EOMONTHPremiumIn B2, use EOMONTH to find the last day of the month of the invoice date in A2.
  3. 3First day of next monthPremiumA subscription starts on the first day of the month after it is signed. In B2, find that date from A2. Take the end of the signing month with EOMONTH and add one day.
  4. 4Days in the month with DAY and EOMONTHPremiumTo prorate the first month's rent you need the number of days in the move-in month. In B2, take the day number of the month's last day, DAY(EOMONTH(A2,0)).

Functions in this topic

Read the guide

Questions

How do I get the first day of the current month in Excel?

Use =EOMONTH(A2,-1)+1. EOMONTH with -1 gives the last day of the previous month, and adding 1 moves to the first day of the month the date is in. To get the first day of today's month, replace A2 with TODAY().

Why does EDATE return a number like 45366?

EDATE returns the date as a day number, and the cell has a number format. Select the cell and choose Short Date on the Home tab. 45366 is Mar 15, 2024. Nothing is wrong with the formula, only with how the cell displays the result.

What is the difference between EDATE and adding 30 days?

Months have different lengths, so +30 drifts: Jan 15 plus 30 days is Feb 14, not Feb 15. EDATE keeps the same day number and moves by calendar months, so it gives Feb 15. Use EDATE for renewals and contracts that run in whole months, and +30 for terms counted in days.