The DATEVALUE function

The DATEVALUE function is a date and time function which converts a date stored a plain text into a valid date format within Excel. It does so by attempting to isolate the month in text form, from the day and year within the cell.

This is the format of the DATEVALUE function:

=DATEVALUE(DATE STRING)

Here is an example of the DATEVALUE function:

=DATEVALUE(“January 1, 2020”) = 01/01/2020

DATEVALUE converting a text string into a valid date format

This function is valuable when you are importing or cleaning data which has been prepared in a non-standard format. While most systems that natively export to Excel will format their dates appropriately, some will keep these values as text, requiring the additional manipulation.

An example of a functional implementation of this would be taking in user responses to a survey where specific fields reporting dates were requested. These dates may not be in standard formats and could need the additional manipulations of the DATEVALUE function to be useful to your analysis. Often times this requires making additional columns for each field which requires modification.

=DATEVALUE(B6) = 26/02/2020