Excel Combine Cells to Make Date: 4 Easy Methods

To combine separate cells into a date in Excel, use the DATE function with YEAR, MONTH, and DAY arguments, or concatenate with the & operator and convert the result. This article explains four reliable methods, when each works best, and how to avoid common errors like text dates or wrong formats.

Formula

The core formula is =DATE(year, month, day). It returns a real Excel date serial number, which you can then format as a date. Each argument must be a number or a cell reference containing a number.

• year – a four-digit year (e.g., 2024). Excel interprets two-digit years as 1900–2099.
• month – a number 1–12. Values outside this range roll over (e.g., 13 becomes January of the next year).
• day – a number 1–31. Values outside the month’s length roll over (e.g., 32 in April becomes May 1).

If your cells contain text like “2024”, “5”, “17”, wrap each in VALUE() or multiply by 1 to convert them to numbers first.

Worked Example

Using the default values from the calculator: year = 2024, month = 5, day = 17.

1. Enter =DATE(2024, 5, 17) in a cell.
2. Excel returns the serial number 45429 (which represents May 17, 2024).
3. Format the cell as a date: right-click → Format Cells → Date → choose a style like 14-Mar-2021 or 3/14/21.

The result is May 17, 2024. The same logic applies if you reference cells: =DATE(A2, B2, C2) where A2 holds the year, B2 the month, and C2 the day.

When to Use Each Method

Your choice depends on how your source data is stored.

• Year, month, day in separate numeric cells – Use =DATE(year, month, day). This is the safest and most flexible.
• Day, month, year in separate cells (non-US order) – Swap arguments: =DATE(C2, B2, A2) if A2=day, B2=month, C2=year.
• Text strings like “2024-05-17” or “May 17, 2024” – Use =DATEVALUE("2024-05-17") to convert text to a date serial number.
• Concatenating with & and then converting – =DATEVALUE(A2 & "/" & B2 & "/" & C2) works but is fragile because it depends on your system’s date settings.

Common Mistakes and How to Avoid Them

These pitfalls cause the most confusion:

• Text dates not recognized – If your result is left-aligned, it’s text. Use VALUE() or -- to coerce numbers stored as text.
• Wrong date order – In the US, month/day/year is standard; in most other countries, day/month/year. Always check your region settings in Excel (File → Options → Advanced → When calculating this workbook).
• Two-digit years – Excel treats 30 as 1930 and 29 as 2029. Use four-digit years to avoid ambiguity.
• Formatting – The cell may show a number like 45429. Apply a date format via Home → Number Format → More Number Formats.
• Using & without DATEVALUE – =A2 & "/" & B2 & "/" & C2 produces a text string, not a date. Wrap it with DATEVALUE() or use DATE().

How to Read the Result

Excel stores dates as serial numbers. January 1, 1900 is 1, and each day adds 1. So May 17, 2024 is 45429. This allows date arithmetic: subtracting two dates gives the number of days between them. To display a readable date, apply a date format. To see the serial number, change the format to General or Number.

If you see #####, the column is too narrow. Widen it or change the format. If you see a number like 45429, the cell is formatted as a number, not a date.

Alternatives to DATE()

Other functions can combine date parts:

• DATEVALUE(text) – Converts a text string that looks like a date. Useful when you already have a text date.
• EDATE(start_date, months) – Adds months to a date, not for combining parts.
• Power Query – Use “Merge Columns” with a delimiter, then change the data type to Date. Good for large datasets.
• Text to Columns – If you have a single column with a date as text, use Data → Text to Columns → Delimited → choose the delimiter, then set the column data format to Date.

For most simple cases, DATE() is the cleanest and most predictable.

Frequently Asked Questions

Why does my formula return a number instead of a date?

Your cell is formatted as General or Number. Right-click the cell, choose Format Cells, select Date, and pick a format. The underlying value is still a valid date serial number.

How do I combine day, month, and year when they are in separate columns?

Use =DATE(year_cell, month_cell, day_cell). For example, if A1=2024, B1=5, C1=17, then =DATE(A1, B1, C1) returns May 17, 2024.

What if my month is stored as text like “May” or “May 17”?

Convert the month name to a number using MONTH(DATEVALUE("1" & month_name & "2024")) or use a lookup table. For “May”, you can use =MONTH("1" & A2 & "2024") if A2 contains “May”.

Can I combine a date and time from separate cells?

Yes. Add the date serial number and the time serial number. For example, if A1 contains a date and B1 contains a time, use =A1 + B1. Format the result as a custom format like dd/mm/yyyy hh:mm.

Why does my date show as #####?

The column is too narrow to display the full date. Widen the column by double-clicking the right edge of the column header, or change the format to a shorter date style.

How do I combine cells with a delimiter like a slash?

Use =A2 & "/" & B2 & "/" & C2 to create a text string, then wrap it with DATEVALUE() to turn it into a real date. However, this is locale-dependent and may fail if your system expects a different order.

Related

• Delta Math Word Problem Solver Algebra: How It Works
• Chemistry Word Problem Solver: How to Work Them Out
• CRC32 Calculator Using Polynomial

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