EDATE
The same day, a number of months away.
Move a date a number of months while keeping the day
A date: the same day of the month, that many months away
Syntax
=EDATE(date, months)
| Argument | Required | What goes here |
|---|---|---|
date | Required | The date to move, usually an invoice date. |
months | Required | How many months forward, or a negative number to go back. |
How to use EDATE
EDATE shifts a date by whole months and keeps the day of the month where it can.
Where it cannot, it takes the last day instead. A 31 January date moved on one month is 28 February, because there is no 31st.
It is the function behind anything released or recognised evenly over months: a prepayment spread over twelve, a deferred income schedule, a hire agreement.
Negative months go backwards, so the same formula builds a history as builds a forecast.
Examples
Every result below is worked out by the same evaluator that marks your answers, on the sheet shown here.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Reporting date | 2026-10-31 | |||
| 2 | |||||
| 3 | Invoice | Invoice date | Terms (days) | Amount | Customer |
| 4 | INV-9001 | 2026-07-14 | 30 | 1200 | Calder Utilities |
| 5 | INV-9002 | 2026-08-29 | 30 | 640 | Brightwell Paper |
| 6 | INV-9003 | 2026-09-03 | 45 | 3100 | Meridian Freight |
| 7 | INV-9004 | 2026-09-30 | 14 | 455 | Thornbury Print |
| 8 | INV-9005 | 2026-10-12 | 30 | 2080 | Calder Utilities |
| 9 | INV-9006 | 2026-10-27 | 60 | 725 | Ashgrove Catering |
| Formula | Result |
|---|---|
=EDATE(B4,12)The same day a year on. This is the last month of a twelve month prepayment that started on the invoice date. | 2027-07-14 |
=EDATE(B4,1)One month on, day kept. | 2026-08-14 |
=EDATE(B1,-3)Three months back from the reporting date, for a quarterly comparison. | 2026-07-31 |
Notes
- EDATE keeps the day and EOMONTH keeps the month end. Which you want depends on whether the agreement is dated or period based.
- Moving a 31st into a short month gives the last day of that month, and moving it on again does not get the 31st back.
Related
A reference tells you what it does. A unit makes you use it.