Convert a date string to a date value

If you have any dates entered as a string (e.g. "01 Jul 2022") you can convert it to an actual date using the DATEVALUE function.
This function returns the date serial number given a date in text format.
Exactly which formula you use will depend on your Regional Format.
If you regional format is set to English (United States) you need to use a different formula.


Regional Format - English (United Kingdom)

 A
1=TODAY() = Tuesday 01 October 2024
2=NOW() = Tuesday 01 October 2024 06:08:01
3=TEXT(A1, "yyyymmdd") = 20241001
4=DATEVALUE(RIGHT(A3, 2)&"/"&MID(A3, 5, 2)&"/"&(LEFT(A3, 4))) = Tuesday, 01 October, 2024
520200523
6=DATEVALUE(RIGHT(A5, 2)&"/"&MID(A5, 5, 2)&"/"&(LEFT(A5, 4))) = Saturday, 23 May, 2020
720200208
8=DATEVALUE(RIGHT(A7, 2)&"/"&MID(A7, 5, 2)&"/"&(LEFT(A7, 4))) = Saturday, 08 February, 2020

1 - Displays the current date. Custom format "dddd dd mmmm yyyy".
2 - Displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss".
3 - Converts the date in cell "A1" to a text string with the format "yyyymmdd".
4 - Converts the text string in cell "A3" to its corresponding date serial number. Custom format "dddd, dd mmmm, yyyy".
5 - Contains the text string "20200523".
6 - Converts the text string in cell "A5" to its corresponding date serial number. Custom format "dddd, dd mmmm, yyyy".
7 - Contains the text string "20200208".
8 - Converts the text string in cell "A7" to its corresponding date serial number. Custom format "dddd, dd mmmm, yyyy".


Regional Format - English (United States)

 A
120200715
2=DATEVALUE(MID(A1,5,2)&"/"&RIGHT(A1,2)&"/"&(LEFT(A1,4))) = Wednesday, 15 July, 2020
320200523
4=DATEVALUE(MID(A3,5,2)&"/"&RIGHT(A3,2)&"/"&(LEFT(A3,4))) = Saturday, 23 May, 2020
520200208
6=DATEVALUE(MID(A5,5,2)&"/"&RIGHT(A5,2)&"/"&(LEFT(A5,4))) = Saturday, 08 February, 2020

1 - Contains the text string "20200715".
2 - Converts the text string in cell "A1" to its corresponding date serial number. Custom format "dddd, dd mmmm, yyyy".
3 - Contains the text string "20200523".
4 - Converts the text string in cell "A3" to its corresponding date serial number. Custom format "dddd, dd mmmm, yyyy".
5 - Contains the text string "20200208".
6 - Converts the text string in cell "A5" to its corresponding date serial number. Custom format "dddd, dd mmmm, yyyy".


Built-in Functions

DATEVALUE - The date serial number given a date in text format.
LEFT - The first or left most characters in a text string.
MID - The characters from the middle of a text string.
NOW - The date serial number of the current system date and time.
RIGHT - The last or right most characters in a text string.
TEXT - The number as a formatted text string.
TODAY - The date serial number representing today's date.


Related Formulas

Return a date in US date format


© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top