Date & Time

DATEVALUE Function in Excel

Converts a date stored as text to a serial number that Excel recognizes as a date.

Syntax

  • =DATEVALUE(date_text)

Arguments

  • date_text (required): Text representing a date

Examples

  • =DATEVALUE("1/1/2024") - Convert text to date - Result: 45292
  • =DATEVALUE("January 1, 2024") - Long format - Result: 45292

Excel DATEVALUE function documentation

  • DATEVALUE converts a date stored as text into a valid Excel date serial number for calculations.
  • Syntax: =DATEVALUE(date_text). Pair it with [DATE](/functions/date/) when you need explicit year-month-day construction.
  • Use DATEVALUE to fix imported CSV dates before filtering, sorting, or building rolling reports.
  • Category reference: [/categories/date-time/](/categories/date-time/).

DATEVALUE syntax and parsing rules

  • date_text is required and should match your locale's recognizable date pattern.
  • If text includes time, DATEVALUE returns the date portion and ignores the time fraction.
  • Use TRIM or CLEAN before DATEVALUE when hidden spaces from imports break parsing.
  • Returns #VALUE! when Excel cannot interpret the text as a date.

Step-by-step: convert imported text dates

  • Step 1 - Place source text dates in column A (for example, 06/12/2026 as text).
  • Step 2 - In B2, use =DATEVALUE(A2).
  • Step 3 - Format column B as Date to display calendar output.
  • Step 4 - Replace original text values with the converted serials if needed.

DATEVALUE vs VALUE vs DATE

  • DATEVALUE parses text dates into serial dates for timeline analysis.
  • VALUE parses general numeric text, but DATEVALUE is clearer for date-specific cleanup.
  • DATE builds dates from numeric parts and is more reliable than locale-dependent text parsing.
  • Related text formatting function: [TEXT](/functions/text/).

Worked examples to copy

  • Simple conversion: =DATEVALUE("2026-06-12").
  • Cell-based parse: =DATEVALUE(A2).
  • Month start from text: =EOMONTH(DATEVALUE(A2),-1)+1.
  • Year extraction after parse: =YEAR(DATEVALUE(A2)).

People also ask

  • Why does DATEVALUE return #VALUE!? - The text is not in a recognizable date format.
  • Does DATEVALUE include time? - No, it returns the date serial only.
  • Can DATEVALUE parse ISO strings? - Usually yes for standard YYYY-MM-DD patterns.
  • DATEVALUE or DATE for templates? - DATE is best when you already have numeric components.

Common errors

  • #VALUE! if text cannot be parsed as date

Use cases

  • Import text dates
  • Data conversion
  • Date parsing

Frequently asked questions

  • Why do I need DATEVALUE when I can just type dates? DATEVALUE converts text that LOOKS like a date but isn't recognized by Excel. Common when importing data from CSV, databases, or web sources where dates come as text strings. The result is a proper date serial number Excel can calculate with.
  • What date formats does DATEVALUE recognize? DATEVALUE recognizes formats based on your system locale: "1/15/2024", "January 15, 2024", "15-Jan-2024", "2024/1/15". If your text uses a different format, you may need to rearrange it first using TEXT functions.
  • How do I convert text dates in non-standard formats? For formats like "20240115" (YYYYMMDD), use =DATE(LEFT(A1,4), MID(A1,5,2), RIGHT(A1,2)). For "15.01.2024" (European), use =DATE(RIGHT(A1,4), MID(A1,4,2), LEFT(A1,2)). Build the date from extracted parts.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use DATEVALUE

  • Import text dates — common Date & Time scenario for DATEVALUE.
  • Data conversion — common Date & Time scenario for DATEVALUE.
  • Date parsing — common Date & Time scenario for DATEVALUE.

DATEVALUE in the Date & Time category

  • Browse all Date & Time functions at /categories/date-time/ for related formulas.
  • DATEVALUE syntax: =DATEVALUE(date_text)
  • date_text (required): Text representing a date
  • Confirm DATEVALUE arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm DATEVALUE arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • DATE (/functions/date/): Creates a date from year, month, and day components.
  • TIMEVALUE (/functions/timevalue/): Converts a time stored as text to a decimal number.
  • VALUE (/functions/value/): Converts a text string that represents a number to a number.

Errors to watch for

  • #VALUE! if text cannot be parsed as date — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter DATEVALUE on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

DATEVALUE worked examples to copy

  • =DATEVALUE("1/1/2024") — Convert text to date. Expected result: 45292.
  • =DATEVALUE("January 1, 2024") — Long format. Expected result: 45292.

DATEVALUE reference summary for crawlers and offline review

  • DATEVALUE belongs to the Date & Time category in Excel. Converts a date stored as text to a serial number that Excel recognizes as a date.
  • Full syntax: =DATEVALUE(date_text). Open /functions/datevalue/ for parameters, FAQs, and related pages.
  • Common mistakes: #VALUE! if text cannot be parsed as date
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.