Build Dates From Month and Year in Excel

Monthly reports, budgets and survey exports often store the month and year in separate columns. One column might hold 3 or March, and another holds 2024. That layout is easy to read, but sorting, filtering and charting work much better when the two pieces become one real date. Excel will not combine them for you automatically.

This guide shows the excel date formulas from month and year that do the job, how to pull the year or month back out of the result, and how to confirm the cells hold true dates. The examples use a small table, so you can type them into a blank sheet and follow along. If your month column contains names such as March, there is one extra step, covered below.

How do you build a date from month and year in Excel? The short answer

Use =DATE(year, month, 1), pointing the first argument at your year cell and the second at your month cell. The final 1 sets the day to the first of the month, which gives Excel a valid date you can sort, filter and format.

Your situation	What to do	Why
Month is a number (1 to 12) and year is a number	=DATE(B2,A2,1)	DATE builds a true date from three numbers.
Month is a name such as Mar	Convert the name to a number with MATCH, then pass it to DATE	DATE needs a number for the month argument.
You need the last day of the month	=EOMONTH(C2,0)	EOMONTH returns the final day, which suits deadlines and month-end totals.
You need the year back from a date	=YEAR(C2)	YEAR returns the four-digit year as a number you can group or compare.

Before you start

Check that the month and year cells are numbers. Values that look like numbers can still be stored as text, and text fails checks like ISNUMBER and can break sorting. Numbers align to the right by default, so a left-aligned value is a warning sign.
Confirm each year has four digits. Excel reads years from 0 to 99 as 1900 to 1999, so a year entered as 24 becomes 1924. Enter 2024 instead.
Decide which day you want. The first of the month is the usual choice for monthly data. If you need the last day, use EOMONTH instead of a fixed 1.
Work in a new column. Leaving the original cells untouched lets you compare the results against the source and undo changes easily.
Export PDF tables to Excel first if needed. If the month and year came from a PDF table, use the PDF editor in the OmniDoc desktop client, which can export to Excel, then check the cells before building dates.

Building the date column step by step

This example uses column A for the month, column B for the year, and column C for the new date. Row 2 holds March 2024.

Enter the month and year. Type 3 in A2 and 2024 in B2. The month is the number, and the year is the full four-digit value.
Enter the DATE formula. Click C2 and type =DATE(B2,A2,1), then press Enter. Excel combines the year, month and day into one date.
Apply a month-and-year format. The cell may show a plain number at first, because Excel stores dates as serial numbers. Open the number format options and choose a pattern such as mmm yyyy, so the cell reads as Mar 2024.
Handle month names if needed. If column A contains text like Mar, enter this in C2 instead: =DATE(B2,MATCH(TRIM(A2),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0),1). MATCH finds the position of the name in the list, which is the month number, and TRIM removes stray spaces that would otherwise cause a #N/A error.
Fill the formula down. Drag the small square at the bottom right of C2 down the column, or double-click it, so every row gets its own date.
Pull the year back out. In D2, enter =YEAR(C2) to return 2024. Use =MONTH(C2) in another column to return the month number. Both results are numbers, so you can group or filter on them.
Find the last day of the month. In E2, enter =EOMONTH(C2,0). If C2 holds March 1, 2024, the result is March 31, 2024. Format the column the same way as the date column.

Check the result

Run these checks once the column is filled, before you use the dates in a report or chart.

Check	How to see it	If it's wrong
Dates are real dates	In a spare cell, enter =ISNUMBER(C2). TRUE means the cell holds a date value.	FALSE means the month or year is text. Re-enter those cells as numbers, then recheck.
Year has four digits	Look at =YEAR(C2) and confirm it returns 2024, not 1924.	Re-enter the year with all four digits.
Month matches the source	Compare =MONTH(C2) with the month in column A.	If the name lookup returns #N/A, check the spelling in the source and remove extra spaces.
Sorting works	Sort the date column from oldest to newest.	If the order is off, some cells are still text. Rebuild those rows with the formula.

❓ FAQ

1. Why does my date show as a number like 45352?

Excel stores dates as serial numbers, so a formula result shows a number until the cell has a date format. Apply a format such as mmm yyyy or a standard date pattern, and the value will display as a date. The underlying number does not change, which is why sorting still works.

2. What happens if I type a two-digit year?

Excel interprets a year from 0 to 99 as 1900 to 1999. So a two-digit 24 becomes 1924 and the result will look wrong in any report. Type the full four-digit year in the source column, and the formula will return the correct date.

3. Can I skip the helper column and put everything in one formula?

Yes. You can nest the month lookup directly inside DATE, for example =YEAR(DATE(B2,MATCH(TRIM(A2),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0),1)), which returns just the year. A helper column is easier to check, though, so keep one if you plan to sort or audit the data later.

📝 Summary

Use =DATE(year, month, 1) to turn separate month and year cells into a real date, and convert month names with MATCH first if needed. Then use YEAR and MONTH to read values back out, and run the ISNUMBER and sort checks before you rely on the results.

Open OmniDoc to work on your spreadsheets