DATE
Build a date from its three parts.
Build a date out of a year, a month and a day
A date
Syntax
=DATE(year, month, day)
| Argument | Required | What goes here |
|---|---|---|
year | Required | The year in full, like 2026. |
month | Required | The month as a number, 1 to 12. |
day | Required | The day of the month. |
How to use DATE
DATE takes three numbers and puts them together into a date. It is the way back from the parts YEAR, MONTH and DAY pull out.
Its useful trick is that it accepts numbers outside the normal range and carries them. Month 13 becomes January of the next year, and day 0 becomes the last day of the previous month.
That is how a period boundary gets built without a table of month lengths, though EOMONTH says the same thing more plainly and is easier to read six months later.
A date that cannot exist is refused rather than shifted.
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 |
|---|---|
=DATE(2026,10,31)The three parts written out. | 2026-10-31 |
=DATE(YEAR(B1),MONTH(B1),1)The first day of the reporting month, for a period that starts there. | 2026-10-01 |
=DATE(YEAR(B4),MONTH(B4)+1,1)The first of the month after the invoice. Month 8 here, but the same formula on a December date rolls into the next year on its own. | 2026-08-01 |
=DATE(YEAR(B1),MONTH(B1)+1,0)Day nought of next month is the last day of this one. EOMONTH does this without the trick. | 2026-10-31 |
Notes
- A date such as 30 February is refused here rather than rolled forward to 2 March.
- Where a period end is what you want, EOMONTH states it. DATE with day nought works and reads as a puzzle.
Related
A reference tells you what it does. A unit makes you use it.