The DATE, YEAR, MONTH guide
=DATE(B2, C2, D2) turns a year, a month and a day in separate cells into one real date. YEAR, MONTH and DAY do the opposite, which lets you build new dates from old ones, like the 15th of next month.
The syntax
=DATE(year, month, day)
=YEAR(date) =MONTH(date) =DAY(date)
The order is always year, month, day, whatever order your country writes dates in. DATE(2025, 12, 3) is 3 December 2025.
Why not just join the numbers?
=C2&"/"&D2&"/"&B2 looks like a date, but it is text. It sits on the left of the cell, it sorts as text, and subtracting it from another date fails. DATE returns a real date.
DATE rolls over
A month of 13 is January of the next year, and a day of 32 runs into the next month. That makes date maths safe at year ends: the 15th of the month after an invoice dated 12/3/2025 is =DATE(YEAR(E2), MONTH(E2)+1, 15), and DATE turns month 13 of 2025 into 1/15/2026. Day 0 is the last day of the month before: DATE(2026, 3, 0) is 2/28/2026.
Watch the year
- A year typed into the formula,
DATE(2026, MONTH(E2)+1, 15), sends a 2025 invoice to 2026. Take it from the date withYEAR(E2). - A year under 1900 is added to 1900, so
DATE(26, 1, 1)is 1/1/1926. Use four-digit years.
Dates in one number
Some systems export 20251128. Take it apart with text functions and rebuild it: =DATE(LEFT(A2,4), MID(A2,5,2), RIGHT(A2,2)).
Common mistakes
- Day, month, year order.
DATE(28, 11, 2025)reads 28 as the year 1928 and lands in 1934. - +30 for "next month". Months have 28 to 31 days, so it drifts.
- EDATE for a fixed day. EDATE keeps the invoice's own day; the payment day here is always the 15th.
For month ends instead: EOMONTH. For whole months later: EDATE.
FAQ
- How do I combine year, month and day into a date in Excel?
- Use =DATE(year,month,day), for example =DATE(B2,C2,D2). The order is always year, month, day.
- How do I get the year, month or day from a date?
- Use =YEAR(A2), =MONTH(A2) and =DAY(A2). Each returns a number.
- What happens if the month in DATE is more than 12?
- It rolls into the next year: DATE(2025,13,15) is 1/15/2026. Days roll over into the next month the same way.
- Why does DATE give a date in the 1900s?
- The year has two digits or the arguments are in the wrong order. Years under 1900 are added to 1900.



































































