Combine Day, Month, and Year in Excel with a Date Formula

Formulas & functions · Updated November 20, 2026

To combine a day, month, and year from separate cells into a real Excel date, use the DATE function. If the day is in A2, the month number is in B2, and the four-digit year is in C2, enter =DATE(C2,B2,A2) in another cell. Format the result as a date to display it clearly. You will need the three source values in separate cells, with the month represented as a number from 1 to 12.

What You’ll Need

  • A worksheet in Excel for Microsoft 365 on Windows, with day, month, and year values in separate cells or columns.
  • The month as a number, such as 3 for March, and preferably a four-digit year, such as 2026.
  • An empty cell for the combined date. If you have multiple rows, leave room to fill the formula down the column.

Step-by-Step

  1. Check how the source values are arranged. Identify which cell contains the day, which contains the month, and which contains the year. The columns do not have to be in a particular order, but you must match each cell to the correct DATE argument. For the example in this guide, A2 contains 14, B2 contains 3, and C2 contains 2026. Those values mean the 14th day, the third month, and the year 2026.
  2. Select an empty result cell. Choose a cell where you want the date to appear, such as D2. Do not type the formula over one of the source values, because the formula needs to refer to those original cells. If your worksheet has headings, use a heading such as Combined date in D1 so the purpose of the new column is clear.
  3. Enter the DATE formula in the correct order. The DATE function takes its arguments in year, month, day order, even though the source columns in this example are day, month, year. With the day in A2, month in B2, and year in C2, enter this in D2: =DATE(C2,B2,A2). Press Enter. With the example values 14, 3, and 2026, the result is March 14, 2026. Excel stores that result as a date serial value, rather than as three pieces of text joined together.
  4. Apply a date display format. Select D2, then go to Home > Number and choose Short Date or Long Date from the number format list. Short Date displays a compact date, while Long Date includes the weekday and spells out the month. You can also open Format Cells by pressing Ctrl+1 on Windows, choose the Number tab, and select Date. The displayed order may vary with your regional settings; the underlying date remains the same.
  5. Use a custom format if you need a particular appearance. In Format Cells, choose Custom and enter a format such as dd/mm/yyyy for a two-digit day, month, and year, or d mmmm yyyy for a day followed by the full month name and year. These are display formats, not formulas: changing the format changes how the date looks, not the date value. Check that the chosen order is clear to anyone who will read the workbook, especially if people use different regional settings.
  6. Copy the formula to other rows. If your data runs down columns A, B, and C, select D2 and drag the small square at the lower-right corner of the cell down through the rows you need. You can also copy D2, select the destination cells, and paste. Excel adjusts the row references automatically, so the next row uses A3, B3, and C3. Review a few results to confirm that each row paired the correct day, month, and year.
  7. Keep incomplete rows blank if needed. A formula will still try to calculate when one of its input cells is empty, which can create a date you did not intend. If you want the result cell to stay blank until all three source cells contain something, use this alternative. With day values in A2:A100, month values in B2:B100, and year values in C2:C100, enter the following in D2 and fill it down: =IF(COUNTA(A2:C2)<3,"",DATE(C2,B2,A2)). The example row with all three values returns March 14, 2026; a row with any blank input returns a blank cell. This checks whether cells are populated, not whether their contents are valid date components.
  8. Check the result before relying on it. Compare the combined date with the source values: March 14, 2026, should correspond to day 14, month 3, and year 2026. If you are processing a large list, inspect entries near the top and bottom and check any rows with unusual values. Once the formula and display format are correct, you can use the dates for sorting, filtering, or date-based calculations.

Common Mistakes

Putting the arguments in day-month-year order. DATE always expects year first, month second, and day third. If the source cells are day in A2, month in B2, and year in C2, the correct references are C2, B2, and A2. A formula that starts with A2 may treat the day as the year and produce a very different result.

Joining the values as text instead of creating a date. Typing a formula that combines the cell contents with spaces or separators may look like a date, but Excel may treat the result as text. Text that resembles a date is not always sortable or usable in date calculations. DATE creates a numeric date value that Excel recognizes.

Using a month name where a month number is expected. DATE is designed for numeric year, month, and day arguments. If the month column contains words such as March rather than 3, convert or map the month to a number before building the date. Do not assume that every spelling or language will be interpreted consistently.

Entering a two-digit year. Use the complete year, such as 2026, rather than 26. DATE interprets year numbers from 0 through 1899 by adding 1900, so a two-digit value can produce a date in an unexpected century. A four-digit year makes the intended result explicit.

Confusing a display problem with a calculation problem. A valid date can appear as a number such as 46095 if the result cell is formatted as General or Number. That number is Excel’s internal date serial. Apply a date format rather than changing the formula.

Assuming Excel rejects impossible combinations. DATE normalizes out-of-range month and day values instead of always reporting an error. For example, a month value of 13 rolls into the following year. This can be useful for date arithmetic, but it can also conceal incorrect source data, so check that months are 1 through 12 and days make sense for the selected month and year.

Tips

  • Keep the source columns and the combined date column separate. This makes it easier to audit the date or correct a source value later.
  • Use four-digit years and numeric months when collecting or importing data. Consistent inputs reduce ambiguity and make the formula easier to copy.
  • Choose a display format that avoids regional confusion. A format with a spelled-out month, such as 14 March 2026, is harder to mistake than a compact value such as 03/04/2026.
  • For a long list, convert the data range to an Excel table with Insert > Table. When you add a formula in a table column, Excel can fill it through the column and extend it to new rows.
  • If you need to share the finished dates without their formulas, copy the result cells and use Home > Paste > Paste Values. Keep a copy of the original formula-based sheet if you may need to change the source values later.
  • Remember that the formula calculates a date value and the number format controls its appearance. You can change the appearance without rebuilding the date.

Troubleshooting

The cell shows a number instead of a date: Select the result cell and choose Short Date or another date format from Home > Number. If the cell still does not look right, open Format Cells with Ctrl+1 and select a date format. The number is usually the stored date serial, not evidence that the DATE formula failed.

The result is the wrong day, month, or year: Check the argument order in the formula. DATE requires year, month, day. Then verify that each reference points to the intended source column and that the source values are not mislabeled. For the example layout, the formula must refer to the year in C2 first, month in B2 second, and day in A2 third.

The result is a date in a different month or year: Look for a month outside 1 through 12 or a day that exceeds the number of days in that month. DATE carries extra days or months into the next period. Correct the source value if it was entered by mistake. Also check whether the year has four digits.

The formula displays an error: Confirm that the input cells contain numeric values and that the formula uses commas between its arguments. If a value was imported as text, convert it to a number before using DATE. You can try selecting the affected source cells and using the warning icon’s Convert to Number option when Excel offers it, or re-enter the values as numbers. Text month names need to be converted to month numbers first.

The date looks reversed on another computer: The date value can be correct while the display convention differs. Use a format with a spelled-out month for a workbook shared across regions, or agree on an explicit format such as day-month-year. A numeric format like 04/05/2026 can be read as April 5 or May 4 depending on local conventions.

Some rows remain blank or show unexpected values: Inspect the source cells in those rows for missing entries, extra spaces, text values, or a shifted reference. If you used the COUNTA version, remember that it only checks that all three cells are nonempty; it does not validate that they contain sensible numbers. Correct the inputs and recheck the date.

Frequently Asked Questions

Can I combine day, month, and year when the columns are in a different order?

Yes. The source columns can be in any order. Refer to the year cell first, the month cell second, and the day cell third in DATE. For example, if the year is in A2, month in B2, and day in C2, use =DATE(A2,B2,C2).

Does DATE create a real Excel date or just date-looking text?

DATE returns an Excel date value, stored as a number and displayed according to the cell’s number format. That means Excel can use it for sorting, filtering, and date calculations. Joining the source values into a text string does not provide the same reliability.

Can I combine a month written as January or March?

DATE expects a numeric month argument, so use 1 for January, 3 for March, and so on. If the month names come from a fixed list, map each name to its month number first. Converting month names can depend on spelling, language, and regional settings, so numeric month values are the most dependable input.

Why does the formula show a number like 46095?

Excel stores dates as serial numbers, and a cell formatted as General may show that underlying number. Apply a date format to display it as a calendar date. The serial is not necessarily an error.

How do I apply the formula to hundreds of rows?

Enter the formula in the first result row and use the fill handle at the cell’s lower-right corner to fill down. Excel updates relative row references as it copies the formula. Check several rows afterward, particularly if the source data contains blank lines or has a different layout partway down.

Does this work in Excel for Mac or Excel for the web?

Yes. DATE and the basic cell-formatting approach work in Excel for Mac and Excel for the web as well as Microsoft 365 for Windows. Ribbon locations and keyboard shortcuts can vary by platform; on Mac, use the number-format controls available on the Home tab or open Format Cells from the cell’s context menu.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.