DATE, YEAR, MONTH and DAY in Excel
By ExcelReady TeamUpdated 6 min read
The short answer
The Excel DATE function builds a real date from three numbers: =DATE(year, month, day), always in that order. YEAR, MONTH and DAY do the opposite and pull one part out of a date, like =YEAR(B2). Because Excel stores dates as serial numbers, the results can be added, subtracted and compared.
Key takeaways
- DATE takes year, month and day in that order, whatever your regional date format.
- YEAR, MONTH and DAY each take one date and return a number.
- Excel stores dates as serial numbers counting days from January 1, 1900, so date maths is just arithmetic.
- DATE rolls over: month 13 becomes January of the next year, and day 0 is the last day of the previous month.
- Always type four-digit years: DATE(24,1,8) gives 1924, not 2024.
On this page+
The Excel DATE function turns a year, a month and a day into one real date. YEAR, MONTH and DAY work the other way round: they take a date and give you back one part of it. Together they let you fix exported dates, find month starts and quarters, and prepare dates for the calculations job tests ask about.
How do Excel dates work?#
Excel stores every date as a whole number called a serial number. It counts days from January 1, 1900, which is
- January 8, 2024 is 45299. The date format only changes how that number looks.
That is why you can do maths with dates: a date plus 7 is a week later, and one date minus another is the number of days between them. It's also why a date sometimes appears as a five-digit number. The value is fine; the cell just has the General format. For the full story, read why Excel dates are really numbers.
How a date is displayed depends on your computer's regional settings. The same date shows as 1/8/2024 in the US and 08/01/2024 in the UK. The serial number underneath is the same. This guide writes dates as "Jan 8, 2024" to avoid confusion.
How to use the Excel DATE function#
DATE builds a date from three numbers:
=DATE(year, month, day)The order is always year, then month, then day, whatever your regional format. That makes DATE the safest way to put a date inside a formula. A date typed as "1/2/2025" can mean January 2 or February 1, depending on the computer.
Exports often split a date into three columns. DATE joins them back together:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Year | Month | Day | Order Date |
| 2 | SO-1041 | 2024 | 1 | 8 | =DATE(B2,C2,D2) → Jan 8, 2024 |
| 3 | SO-1042 | 2024 | 2 | 19 | Feb 19, 2024 |
| 4 | SO-1043 | 2024 | 3 | 4 | Mar 4, 2024 |
Don't join the parts with &, as in =C2&"/"&D2&"/"&B2. That gives text that looks like a date, but Excel can't
sort it or do date maths with it.
How to use YEAR, MONTH and DAY#
Each one takes a single date and returns a number:
| Function | Returns | Example for Mar 17, 2021 |
|---|---|---|
=YEAR(B2) | The year | 2021 |
=MONTH(B2) | The month, 1 to 12 | 3 |
=DAY(B2) | The day of the month, 1 to 31 | 17 |
They are useful for grouping and filtering, such as a "hire year" column for a report. MONTH returns a number, not a
name. For the name, use TEXT: =TEXT(B2,"mmmm") gives March, and "mmm" gives Mar. TEXT returns text in the
language of your Excel settings.
A popular interview question is the quarter of a date. Divide the month by 3 and round up:
=ROUNDUP(MONTH(B2)/3,0)| Employee | Hire Date | Quarter |
|---|---|---|
| Priya Shah | Mar 17, 2021 | 1 |
| Marcus Lee | Nov 4, 2019 | 4 |
| Elena Rossi | Jul 28, 2022 | 3 |
| Aisha Khan | May 22, 2023 | 2 |
How to combine DATE with YEAR, MONTH and DAY#
The real power comes from using them together. Take a date apart, change one piece, and build it again:
| Goal | Formula | Result for Mar 17, 2021 |
|---|---|---|
| First day of the month | =DATE(YEAR(B2),MONTH(B2),1) | Mar 1, 2021 |
| Last day of the month | =DATE(YEAR(B2),MONTH(B2)+1,0) | Mar 31, 2021 |
| Same date next year | =DATE(YEAR(B2)+1,MONTH(B2),DAY(B2)) | Mar 17, 2022 |
| January 1 of that year | =DATE(YEAR(B2),1,1) | Jan 1, 2021 |
The "last day" formula uses a rule worth knowing: DATE rolls over. Day 0 means the day before the 1st, so it
lands on the last day of the previous month. In the same way, =DATE(2024,13,1) is Jan 1, 2025, and
=DATE(2024,1,32) is Feb 1, 2024.
Rolling over can also surprise you. Adding 6 months with =DATE(YEAR(B2),MONTH(B2)+6,DAY(B2)) to Aug 31 asks for
February 31, which rolls into early March. For whole months, EDATE and EOMONTH are safer. See
EDATE and EOMONTH for adding months and finding month-ends.
What are the common date function mistakes?#
These cause most wrong dates:
| Mistake | What happens | Fix |
|---|---|---|
Two-digit year: =DATE(24,1,8) | Jan 8, 1924 | Type 2024 |
Day and month swapped: =DATE(B2,D2,C2) | Wrong date, or a rolled-over one | Year, month, day |
| A result like 45299 | Looks like a number | Apply a date format |
| A date stored as text | YEAR returns #VALUE! | Convert it to a real date |
| A date typed in quotes in a formula | Depends on regional settings | Use DATE(2024,1,8) |
Excel adds 1900 to any year from 0 to 1899, which is why a two-digit year lands in the 1900s.
To spot a date stored as text, check its alignment: by default, real dates sit on the right of the cell and text sits
on the left. =ISNUMBER(B2) returns TRUE for a real date.
Useful shortcuts on Windows: Ctrl+Shift+# applies a date format, Ctrl+Shift+~ shows the serial number in General format, and Ctrl+; types today's date as a fixed value. On a Mac, use Control instead of Ctrl for the same three shortcuts.
Where to go next with dates#
Once you can build and split dates, most date questions are one more step:
- Days, months or years between two dates: subtract them, or use DATEDIF. See how to count the days between two dates and the DATEDIF function guide.
- Working days only, skipping weekends and holidays: see NETWORKDAYS and WORKDAY.
- Today's date that updates on its own: see TODAY vs NOW in Excel. For practice files and reports with a fixed "as of" date, use DATE instead, so the answers don't change tomorrow.
Practise it#
Try the quarter exercise on this page, then practise DATE, YEAR, MONTH and DAY in a live spreadsheet. You'll rebuild split dates, see the serial numbers underneath, and work out the first day of each hire month.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
Finance wants the hire quarter (1 to 4) for each employee. A quarter is three months, so divide the month by 3 and round up. In C2, type =ROUNDUP(MONTH(B2)/3,0) and press Enter.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Quarter | |||
| 2 | ||||
| 3 | ||||
| 4 | ||||
| 5 | ||||
| 6 | ||||
| 7 | ||||
| 8 | ||||
| 9 |
Tip: MONTH(B2) gives a number from 1 to 12. Divide it by 3, then ROUNDUP to 0 decimal places.
Practise this in a lesson
Frequently asked questions
How do I use the DATE function in Excel?
Type =DATE(year, month, day) with numbers or cell references, like =DATE(2024,1,8) or =DATE(B2,C2,D2). The result is a real date that Excel can calculate with.
Why does my DATE formula show a number like 45299?
That is the date's serial number. The cell has the General format. Apply a date format with Ctrl+Shift+# or from the Number Format list on the Home tab.
How do I get the first day of the month in Excel?
Use =DATE(YEAR(B2),MONTH(B2),1). It keeps the year and month of the date in B2 and sets the day to 1.
How do I get the month name instead of the month number?
MONTH always returns a number from 1 to 12. For the name, use TEXT: =TEXT(B2,"mmmm") gives the full name and "mmm" gives a short one like Mar. The language follows your Excel settings.
Why does DATE(24,1,8) give 1924?
Excel adds 1900 to any year from 0 to 1899, so 24 becomes 1924. Always type the full four-digit year.