Eric Campbell 4 min read Excel

How to Use DATEVALUE in Excel

Excel stores dates as serial numbers—integers where 1 represents January 1, 1900, and each subsequent day increments by one. When you type '12/25/2023' into a cell, Excel automatically converts it to...

Key Insights

  • DATEVALUE converts text strings into Excel serial numbers (integers representing days since January 1, 1900), enabling proper date calculations and sorting that text-formatted dates cannot support
  • The function recognizes most common date formats automatically, but fails with ambiguous formats, times-only strings, or formats incompatible with your system’s regional settings
  • Combining DATEVALUE with IFERROR and other date functions creates robust data processing workflows that handle imported data gracefully without manual cleanup

Understanding DATEVALUE and Why It Matters

Excel stores dates as serial numbers—integers where 1 represents January 1, 1900, and each subsequent day increments by one. When you type “12/25/2023” into a cell, Excel automatically converts it to the serial number 45285. This system enables date arithmetic: you can subtract dates to find duration, add numbers to dates to project forward, and use dates in formulas.

The problem arises when dates enter your spreadsheet as text—from CSV imports, database exports, or copy-paste operations from other applications. Text that looks like “12/25/2023” cannot be used in calculations. You cannot sort it chronologically, calculate days between dates, or use it with date functions. DATEVALUE solves this by converting text strings into proper Excel date serial numbers.

I use DATEVALUE constantly when cleaning imported data. Financial systems, CRM exports, and web scraping often produce dates as text strings. Without conversion, your analysis breaks down immediately.

Basic Syntax and How Excel Interprets Dates

The DATEVALUE function uses simple syntax:

=DATEVALUE(date_text)

The date_text parameter accepts any text string that Excel can interpret as a date. Excel recognizes several formats based on your system’s regional settings:

  • MM/DD/YYYY: “12/25/2023”
  • DD-MMM-YYYY: “25-Dec-2023”
  • MMM DD, YYYY: “December 25, 2023”
  • MM/DD/YY: “12/25/23”

Here’s a basic conversion:

=DATEVALUE("12/25/2023")
// Returns: 45285

To see the actual date instead of the serial number, format the cell as a date using Ctrl+1 (Windows) or Cmd+1 (Mac), then select your preferred date format.

This example shows the conversion in action:

// Cell A1 contains: "03/15/2024" (as text)
// Cell B1 formula:
=DATEVALUE(A1)
// Result: 45370 (formatted as a date shows 3/15/2024)

Practical Use Cases for Data Cleanup

DATEVALUE shines when dealing with imported data. Here’s a realistic scenario: you export transaction data from your accounting system, and the date column arrives as text strings.

// Column A contains text dates:
// A2: "01/15/2024"
// A3: "02/20/2024"
// A4: "03/10/2024"

// Column B formula (starting at B2):
=DATEVALUE(A2)
// Copy down the column

Another common situation involves concatenating date components from separate columns:

// Column A: Year (2024)
// Column B: Month (3)
// Column C: Day (15)

// Combine and convert:
=DATEVALUE(B2&"/"&C2&"/"&A2)
// Result: 45370 (3/15/2024)

When working with dates that include month names, DATEVALUE handles them naturally:

=DATEVALUE("March 15, 2024")
// Returns: 45370

=DATEVALUE("15-Mar-2024")
// Returns: 45370

For bulk conversions, you can combine DATEVALUE with array operations. If you have hundreds of text dates, this approach processes them all at once:

// For Excel 365/2021 with dynamic arrays:
=DATEVALUE(A2:A100)
// Converts all dates in the range simultaneously

Handling Different Date Formats and Regional Settings

Date format interpretation depends on your system’s regional settings. In the US, “3/4/2024” means March 4th. In Europe, it means April 3rd. DATEVALUE follows your system settings, which creates problems when importing data from sources using different conventions.

For unambiguous date formats, use month names:

=DATEVALUE("4-Mar-2024")
// Always interprets as March 4th, regardless of regional settings

When dealing with known European format data on a US system, you need to restructure the text:

// Cell A1 contains: "15/03/2024" (European format: day/month/year)
// This would fail or misinterpret on US systems

// Solution: rearrange the text components
=DATEVALUE(MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,4))
// Converts to: 03/15/2024, then to serial number

DATEVALUE ignores time components if they’re present:

=DATEVALUE("03/15/2024 14:30:00")
// Returns: 45370 (time portion ignored)

For partial dates, you need to provide complete information:

// This fails:
=DATEVALUE("March 2024")
// Returns: #VALUE! error

// This works (assumes first day of month):
=DATEVALUE("March 1, 2024")
// Returns: 45352

Troubleshooting Errors and Building Robust Formulas

The #VALUE! error appears when DATEVALUE cannot interpret the text string. Common causes include:

  1. Completely invalid formats: Numbers alone, times without dates, or non-date text
  2. Regional format mismatches: Ambiguous dates like “13/01/2024” on US systems
  3. Extra characters: Leading/trailing spaces or special characters

Here’s how to handle errors gracefully:

// Basic error handling:
=IFERROR(DATEVALUE(A2),"Invalid Date")

// More sophisticated approach - try multiple formats:
=IFERROR(DATEVALUE(A2),IFERROR(DATEVALUE(MID(A2,4,2)&"/"&LEFT(A2,2)&"/"&RIGHT(A2,4)),"Check Format"))

Leading or trailing spaces break DATEVALUE. Clean the text first:

=DATEVALUE(TRIM(A2))
// TRIM removes extra spaces

When dealing with mixed format data, use nested IFERROR to try multiple interpretations:

=IFERROR(
    DATEVALUE(A2),
    IFERROR(
        DATEVALUE(TEXT(A2,"MM/DD/YYYY")),
        IFERROR(
            DATEVALUE(MID(A2,4,2)&"/"&LEFT(A2,2)&"/"&RIGHT(A2,4)),
            "Unable to convert"
        )
    )
)

Advanced Techniques for Power Users

Combine DATEVALUE with TIMEVALUE when importing datetime strings:

// Cell A1: "03/15/2024 14:30:00"

// Extract date and time separately, then combine:
=DATEVALUE(LEFT(A1,10)) + TIMEVALUE(RIGHT(A1,8))
// Returns: 45370.604166667 (date + time as decimal)

Create dynamic date ranges using TODAY():

// Calculate dates relative to today
=DATEVALUE("01/01/2024") - TODAY()
// Returns: number of days between January 1, 2024 and today

// First day of current month:
=DATEVALUE(TEXT(TODAY(),"MM/01/YYYY"))

Build a date validation system that checks if text can be converted:

// Column A: Text dates
// Column B: Validation formula
=IF(ISNUMBER(DATEVALUE(A2)),"Valid","Invalid")

Process multiple date columns simultaneously with array formulas:

// For Excel 365 - convert multiple columns at once:
=DATEVALUE(A2:C2)
// Converts three adjacent cells containing text dates

Combine with conditional logic for complex data processing:

// Convert only if the text matches expected pattern
=IF(LEN(A2)=10,DATEVALUE(A2),IF(LEN(A2)=8,DATEVALUE(TEXT(A2,"00/00/0000")),"Error"))

Create a helper function using LET (Excel 365) for cleaner formulas:

=LET(
    rawDate, TRIM(A2),
    cleanDate, SUBSTITUTE(SUBSTITUTE(rawDate,"-","/"),".","/"),
    IFERROR(DATEVALUE(cleanDate),"Invalid")
)

This approach first cleans the input by trimming spaces and standardizing delimiters, then attempts conversion with error handling—all in one readable formula.

DATEVALUE remains essential for anyone working with imported data. Master it alongside TEXT, DATEVALUE’s inverse for converting dates back to formatted text strings, and you’ll handle any date-related data challenge Excel throws at you.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.