Date & TimeLevel: IntermediateAvailable in: Excel 2007+
In this article
- Syntax
- Examples
- Example 1
- Example 2
- Common errors and fixes
- Related functions
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. |
DATE · VALUE · TEXT
📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.
✨ Ask AI about this articleStuck on a step? Ask a question and the AI answers using this article.
Free · AI can be wrong