Excel data cleaning interview question
Extra spaces, names in the wrong order, numbers stored as text and dates that aren't dates: the cleaning task interviewers love, solved with formulas, and a sheet that grades yours.
DATE function
DATE builds a date from a year, a month and a day: =DATE(2026,3,9) is 9 March 2026. It's the safe way to make a date in a formula, because it means the same date on every computer, whatever its date settings.
Available in every version of Excel, Excel for the web and Google SheetsKey takeaways
- =DATE(year, month, day) builds a real date, the same on every computer.
- Excel stores a date as a count of days. In the 1900 date system, Excel's default, 1 is 1 January 1900 and 46023 is 1 January 2026. Format the cell as a date to see it as one.
- Months and days past their range roll over, so =DATE(YEAR(A2),MONTH(A2)+1,0) is the last day of A2's month.
- Always give the year in full: DATE reads 26 as 1926.
On this page
DATE syntax
=DATE(year, month, day)| Argument | Required | What it means |
|---|---|---|
year | Yes | The year, in full: 2026. Excel adds 1900 to a year from 0 to 1899, so 26 means 1926. Below 0, or from 10000 up, DATE returns #NUM!. |
month | Yes | 1 to 12. A bigger number runs on into the years after, so 14 is February of the next year; 0 or less goes back, so 0 is December of the year before. |
day | Yes | 1 to 31. A day past the end of the month runs on into the months after; 0 is the last day of the month before, and below 0 goes further back. |
Examples
Build a date from three columns
Exports often split a date into a year, a month and a day. DATE puts them back together as a real date you can sort, filter and subtract.
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Year | Month | Day | Date | |
| 2 | 2026 | 3 | 9 | 9 Mar 2026 |
=DATE(A2,B2,C2) → 9 Mar 2026The first day of a date's month
YEAR and MONTH take A2 apart, and DATE rebuilds it with the day set to 1.
| Row | A | B |
|---|---|---|
| 1 | Date | Month starts |
| 2 | 27 Aug 2026 | 1 Aug 2026 |
=DATE(YEAR(A2),MONTH(A2),1) → 1 Aug 2026The last day of a month, with day 0
Day 0 of March is the day before 1 March: the last day of February. EOMONTH does the same in one step.
| Row | A | B |
|---|---|---|
| 1 | Date | Month ends |
| 2 | 10 Feb 2026 | 28 Feb 2026 |
=DATE(YEAR(A2),MONTH(A2)+1,0) → 28 Feb 2026Months that run past December
November plus three months is month 14 of 2026, which DATE carries over into February 2027.
| Row | A | B | C |
|---|---|---|---|
| 1 | Start | Months | Review |
| 2 | 15 Nov 2026 | 3 | 15 Feb 2027 |
=DATE(YEAR(A2),MONTH(A2)+B2,DAY(A2)) → 15 Feb 2027Practise it
New data, so you can't copy the answer above. We check the number your formula returns, then change the data to make sure it still holds.
Your task: A system exported the date in A2 as one number: year, month, day. In B2, turn it into a real date.
Graded live| Row | A | B |
|---|---|---|
| 1 | Imported | Date |
| 2 | 20260309 |
Write the formula in the bar above, then press Check or Enter.
Hint
The year is LEFT(A2,4), the month MID(A2,5,2) and the day RIGHT(A2,2). DATE takes them in that order.
Solution
=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)) Any formula that returns the same result passes.
Common mistakes
- A two-digit year. =DATE(26,3,9) is 9 March 1926: Excel adds 1900 to a year from 0 to 1899. Give the year in full.
- Building a date as text. "3/9/2026" is 9 March on a US computer and 3 September on a British one, and text that doesn't fit the computer's date settings breaks date maths with #VALUE!. DATE has no such doubt.
- A date that shows as a number. 46090 is a date without a date format. Format the cell as a date (Format Cells > Date) and it shows as one again.
- Adding a month by hand on the 31st. =DATE(2026,1+1,31) asks for 31 February, which runs on to 3 March. To move by whole months and stay inside the month, use EDATE.
How Excel stores dates
A date is a whole number of days. In the 1900 date system, the one Excel uses by default, 1 is 1 January 1900 and =DATE(2026,1,1) is 46023. That’s why subtracting two dates gives the days between them, and why adding 7 to a date gives the same weekday a week later. A time is the fraction of a day after the point: 0.5 is noon.
A workbook can be switched to the 1904 date system (on Windows, File > Options > Advanced), the default in older versions of Excel for Mac. It counts from 1 January 1904, so every date’s serial number is 1,462 lower, and dates copied between the two kinds of workbook move by four years and a day.
DATE is the safest way to put a date into a formula. A typed date such as “3/9/2026” is read by the computer’s own date settings, so it can mean March on one computer and September on another, and if it doesn’t fit them it stays text.
The 1900 leap-year bug
Excel counts 29 February 1900 as a real day, serial number 60, though 1900 wasn’t a leap year. Lotus 1-2-3 made that mistake first, and Excel kept it so files could move between the two. In the 1900 date system, every date from 1 March 1900 on is right. Before it, WEEKDAY gives the wrong day of the week: Excel calls 1 January 1900 a Sunday, but it was a Monday.
FAQ
Why does my date show as a number?
Excel stores dates as numbers, and the cell has no date format. Format it as a date and the number shows as the date it stands for.
What's the earliest date Excel can use?
1 January 1900, which is serial number 1 in the 1900 date system, Excel's default. An earlier date can't be stored as a date; typed in, it stays text. A workbook set to the 1904 date system starts on 1 January 1904 instead.
Is 1900 a leap year in Excel?
Excel treats it as one, although it wasn't: it counts a 29 February 1900. Microsoft kept that for compatibility with Lotus 1-2-3. Only dates before 1 March 1900 are affected.
How do I get today's date?
=TODAY() returns today's date and changes every day. Press Ctrl+; to type today's date as a value that stays put.
Guides that use DATE
Worked problems with practice sheets.
Working days and due dates: Excel interview question
Invoices due at the end of next month, and the working days a client has to pay: the date task interviewers set, solved with EOMONTH and NETWORKDAYS, plus a sheet that grades yours.
#NUM! error in Excel: what it means and how to fix it
#NUM! means a calculation has no valid answer: an IRR with no rate to find, a square root of a negative. Each cause and its fix, with a practice sheet.
Know DATE? Prove it on a real sheet.
Practise DATE and the functions around it in a live grid that grades your result and explains your mistakes.
Runs in your browser · Graded on your result · No install