📎 This article includes 1 downloadable practice file ↓
In this article
DATEVALUE turns a date written as text into a real date number. It follows your Windows regional settings, which is exactly why it sometimes “fails”.
Syntax
=DATEVALUE(date_text)| Argument | What it means |
|---|---|
date_text |
Text such as “15-Aug-2017” or “2017-08-15”. |
Examples
Example 1
=DATEVALUE("2017-08-15")ISO format (yyyy-mm-dd) works on every computer.
Example 2
=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))Reliable for dd/mm/yyyy text regardless of settings.
Common errors and fixes
| You see | Why, and the fix |
|---|---|
#VALUE! |
Text in dd/mm/yyyy on a PC set to US format (mm/dd). Use the DATE/LEFT/MID pattern instead. |
DATEVALUE in practice
Where you will use it
- Turn text dates from CSV exports into real dates
- Fix dates that won't sort
- Compare text dates with real ones
Worked example
| Text date | Formula | Result |
|---|---|---|
| 2026-10-04 | =TEXT(DATEVALUE(A2),"dd-mmm-yyyy") | 04-Oct-2026 |
| 15-Aug-2026 | =TEXT(DATEVALUE(A3),"dd-mmm-yyyy") | 15-Aug-2026 |
| 1 Jan 2027 | =TEXT(DATEVALUE(A4),"dd-mmm-yyyy") | 01-Jan-2027 |
Results calculated in Excel.
Mistakes people make
| Mistake | What to do instead |
|---|---|
| Day-month order depends on Windows settings | Use ISO text yyyy-mm-dd, which is unambiguous |
| Result shows as a number | Format the cell as a date |
Related functions
📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.
⚡ Interactive Practice Lab & Formula Tester:
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
📎 Practice files for this article
- 📗Practice workbook: Dates & times22 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.⬇ XLSX · 34 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
✨ Ask AI about this article
Stuck on a step? Ask a question and the AI answers using this article.
Free · AI can be wrong