DATEVALUE Function – Text to Date in Excel, VBA, G Sheets

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on November 6, 2023
Download Example Workbook

Download the example workbook

This tutorial demonstrates how to use the Excel DATEVALUE Function in Excel to convert a date stored as text into a date serial number.
DATEVALUE Main Function


Convert Date Stored as Text to Date

When working in spreadsheets it’s common to encounter dates stored as text.  You can usually identify dates stored as text because the dates are left-aligned and are proceeded by apostrophes:

DATEVALUE Store as text

These “dates” are treated as text and you’ll be unable to work with them like normal dates.

One way to convert the text to a date is with the DATEVALUE Function:


DATEVALUE FunctionThings to know about the DATEVALUE Function:

  • DATEVALUE ignores time information. To convert a date time stored as text to a number, use the VALUE Function instead. Or use the TIMEVALUE function to get the time only.
  • If the year is omitted, DATEVALUE will assume the current year.

DATEVALUE in Google Sheets

The DATEVALUE Function works exactly the same in Google Sheets as in Excel:

DATEVALUE Google Sheet


You can also use the DATEVALUE function in VBA. Type:


For the function arguments (date_text, etc.), you can either enter them directly into the function, or define variables to use instead.

Lets use the DATEVALUE function from within VBA for the following  cells

Vba DATEVALUE function


DateValue(Range("A1")) 'Will return 12/31/15
DateValue(Range("A2")) 'Will return 07/06/20
DateValue(Range("A3")) 'Will return 02/11/20
DateValue(Range("A4")) 'Will return 02/05/20

Have in mind that the results returned by this function might be different on your PC, as it depends on the regional settings used by your machine.

AI Formula Generator

Try for Free

Excel Practice Worksheet

practice excel worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!

Free Download

Return to List of Excel Functions