Excel Get Date From Day Month Year

This guide shows you how to combine separate day, month, and year values into a real Excel date using the DATE function. You enter three numbers (or cell references) and get back a serial date that Excel can format, sort, and use in calculations.

Formula

The core formula is =DATE(year, month, day). It takes three separate numbers and returns a valid Excel date serial number. The syntax is:

• year – a four-digit number (e.g., 2024). Excel interprets 0–1899 by adding 1900, so 24 becomes 1924, not 2024.
• month – a number from 1 to 12. Values outside this range roll over: month 13 becomes January of the next year, month 0 becomes December of the previous year.
• day – a number from 1 to 31. Values outside the month length roll over similarly: day 32 in January becomes February 1.

Excel stores dates as serial numbers where 1 = January 1, 1900. The DATE function converts your three inputs into that serial number, which you can then format as a date using the Number Format dropdown.

Worked Example

Using the default values from the calculator above: day = 15, month = 6, year = 2024.

1. Start with the formula: =DATE(2024, 6, 15)
2. Excel calculates the serial number for June 15, 2024: 45458.
3. If the cell is formatted as General, you will see 45458. Apply the Short Date format (Ctrl+Shift+# on Windows) to display 6/15/2024.

The result matches the calculator output: June 15, 2024.

Why Use DATE Instead of Concatenation

Many users try to build a date with =A1&"/"&B1&"/"&C1. That creates a text string, not a date. Text dates cannot be sorted chronologically, used in date arithmetic (like =TODAY()-A1), or formatted as regional date styles. The DATE function produces a true numeric date, so all date features work.

Another common approach is =DATEVALUE("6/15/2024"), but that requires a text string and depends on your system’s date settings. DATE is locale-independent and always works with numeric inputs.

Handling Text Inputs

If your day, month, or year cells contain text (e.g., “15” as text), DATE will still accept them because Excel coerces text numbers automatically. However, if a cell contains “15 June” or “June 15”, DATE will return a #VALUE! error. In that case, extract the numbers first:

• =DAY(A1) gets the day from a real date.
• =MONTH(A1) gets the month number.
• =YEAR(A1) gets the year.

Then feed those into DATE. This is useful when you import data that arrives as text dates and need to convert them to real dates.

Common Mistakes and Fixes

• Year 1900 leap year bug: Excel treats 1900 as a leap year, so serial number 60 is February 29, 1900, which does not exist. This only affects dates before March 1, 1900. For modern dates, it is irrelevant.
• Two-digit years: Entering 24 gives 1924, not 2024. Always use four-digit years for clarity.
• Month/day swap: If your data uses day-month-year order, but you enter month-day-year, the result is wrong. Check your source order before building the formula.
• Formatting: The formula returns a serial number. If you see 45458, the cell is not formatted as a date. Open Format Cells > Number > Date and choose your preferred style.
• Negative or zero day: Day 0 returns the last day of the previous month. This can be useful for month-end calculations, but it confuses beginners.

Alternatives to DATE

If you have a full date in one text cell, use =DATEVALUE(A1) to convert it, but be aware of locale issues (e.g., US vs. UK date order). For splitting a date into parts, use DAY, MONTH, YEAR. For combining parts with time, use =DATE(y,m,d)+TIME(h,min,sec). If you need the last day of a month, use =EOMONTH(DATE(y,m,1),0). For building a date from a week number and year, use =DATE(y,1,1)-WEEKDAY(DATE(y,1,1),2)+(weeknum-1)*7+1 – but that is more advanced and error-prone.

Frequently Asked Questions

Why does my DATE formula return a number like 45458?

Excel stores dates as serial numbers. The cell is formatted as General, so it shows the raw number. Select the cell, press Ctrl+1, choose Date, and pick a format. The underlying value remains the serial number, but the display becomes a date.

Can I combine day, month, and year that are in separate columns?

Yes. If day is in A2, month in B2, and year in C2, use =DATE(C2, B2, A2). The formula works with cell references exactly as with hardcoded numbers. Drag down to fill the column.

What if my month is a name like “June” instead of a number?

Convert it first. If the month name is in B2, use =MONTH(DATEVALUE(B2&" 1")) to get the month number. Then combine with the day and year. Alternatively, use =DATE(y, MONTH(DATEVALUE(B2&" 1")), d) in one formula.

Does DATE work with years before 1900?

No. Excel’s date system starts at January 1, 1900. For earlier years, you would need to store them as text or use a different system. The DATE function will either return a #NUM! error or an incorrect serial number for years before 1900.

How do I get the date from separate cells but keep the time too?

If you also have hours, minutes, and seconds, use =DATE(y,m,d)+TIME(h,min,sec). The TIME function returns a fractional day, and adding it to the date gives a datetime value. Format the cell as “m/d/yyyy h:mm” to see both.

Why does Excel show 2024 as 1924 when I type 24?

Excel’s DATE function interprets two-digit years as 1900+year. So 24 becomes 1924. To force 2024, always type the full four-digit year. This is a common source of off-by-100-year errors in older spreadsheets.

Related

• ESP32 Alternatives for Rebooting a Home Server Remotely
• Clone SD Card to Image: Full Guide
• Disable Online Speech Recognition in Windows 10

in d | Day | - | 15 | 1
in m | Month | - | 6 | 1
in y | Year | - | 2024 | 1900
out date | Date | - | DATE(y,m,d) | 0