How to create a date from separate year, month and day columns in Excel

See it work

The formula is already in D2, filled down to D6. Change any input and the result updates, or edit the formula to experiment.

How it works

In Google Sheets

The same formula works in Google Sheets: =DATE(A2,B2,C2). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

Common mistakes

• A two-digit year like 26 becomes 1926 in Excel. Use four-digit years.
• Joining the parts with & gives text, not a date, until you convert it.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Related answers

• How to add days to a date in ExcelAdd the number to the date: =B2+C2. Dates are counts of days, so this moves the date forward that many days.
• How to add weeks to a date in ExcelMultiply the weeks by 7 and add them: =B2+C2*7.
• How to calculate the days left in the year in ExcelSubtract the date from December 31: =DATE(YEAR(A2),12,31)-A2.
• How to calculate the days until the next birthday in ExcelBuild this year's birthday, move it to next year if it has passed, and subtract today: =DATE(YEAR($F$1)+(TEXT(B2,"mmdd")<TEXT($F$1,"mmdd")),MONTH(B2),DAY(B2))-$F$1.
• How to check if a cell contains a date in ExcelReal dates are numbers, so =ISNUMBER(A2) is TRUE for a date and FALSE for text that only looks like one.
• How to check if a year is a leap year in ExcelAsk DATE for February 29: =IF(MONTH(DATE(A2,2,29))=2,"Leap year","Common year"). In a common year it rolls over to March.

Functions used: DATE

Lessons: DATE, YEAR, MONTH & TEXT (Pro)