Three date functions that each solve a different problem — building dates, converting text to dates, and formatting dates as text.
Excel stores dates as serial numbers internally. 1 Jan 2026 is stored as 46023. The date you see is just formatting applied to that number. Understanding this makes DATE, DATEVALUE, and TEXT much easier to understand.
=DATE(year, month, day)
=DATE(2026, 9, 19) builds the date 19 September 2026
=DATE(2026, 13, 1) Excel handles overflow: becomes 1 Jan 2027
=DATE(YEAR(A2), MONTH(A2)+1, 1) first day of next monthUse DATE when you have year, month, and day as separate numbers and need to combine them into a real date. Also useful for dynamic date calculations.
=DATEVALUE(date_text)
=DATEVALUE("19 September 2026") returns the serial number for that date
=DATEVALUE("2026-09-19") same result, ISO format
=DATEVALUE(A2) converts text date in A2 to a real dateDATEVALUE converts a date stored as text into a real Excel date number. Use it when you've imported data and dates came in as text strings that Excel doesn't recognise as dates.
DATEVALUE returns a serial number (like 46113). Apply date formatting (Ctrl+1 → Date) to see it displayed as a date.
=TEXT(value, format_code)
=TEXT(A2, "DD/MM/YYYY") "19/09/2026"
=TEXT(A2, "MMMM YYYY") "September 2026"
=TEXT(A2, "DDD") "Fri"
=TEXT(TODAY(), "DD MMM YYYY") "19 Sep 2026"TEXT converts a date (or number) into a formatted text string. Use it when you need to embed a date inside a text formula or display it in a specific format that standard date formatting can't achieve.
| Function | Direction | Use when |
|---|---|---|
| DATE | Numbers → Date | You have year, month, day separately |
| DATEVALUE | Text → Date | Dates came in as text from imports |
| TEXT | Date → Text | You need a date in a specific text format |
If you write ="Report as of "&A2 and A2 is a date, you'll get a number (like "Report as of 46113"). Fix it with TEXT: ="Report as of "&TEXT(A2,"DD MMM YYYY")
Practice this formula yourself — type it in a real spreadsheet and get instant feedback. Free, no download needed.
Start the Excel Basics track free →