Convert a date string to a date value

If you have any dates entered as a string (e.g. "20050701" in the format "yyyymmdd") you can convert it to the corresponding date serial number.
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)

Cell "A1" displays the current date. Custom format "dddd dd mmmm yyyy".
Cell "A2" displays the current date and time. Custom format "dddd dd mmmm yyyy hh:mm:ss".
Cell "A3" converts the date in cell "A1" to a text string with the format "yyyymmdd".
Cell "A4" converts the text string in cell "A3" to its corresponding date serial number. Custom format "dddd, dd mmmm, yyyy".
Cell "A5" contains the text string "20200523".
Cell "A6" converts the text string in cell "A5" to its corresponding date serial number. Custom format "dddd, dd mmmm, yyyy".
Cell "A7" contains the text string "20200208".
Cell "A8" converts the text string in cell "A7" to its corresponding date serial number. Custom format "dddd, dd mmmm, yyyy".

 A
1=TODAY() = Sunday 01 May 2022
2=NOW() = Sunday 01 May 2022 18:37:54
3=TEXT(A1,"yyyymmdd") = 20220501
4=DATEVALUE(RIGHT(A3,2)&"/"&MID(A3,5,2)&"/"&(LEFT(A3,4))) = Sunday, 01 May, 2022
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

Regional Format - English (United States)

Cell "A1" contains the text string "20200715".
Cell "A2" converts the text string in cell "A1" to its corresponding date serial number. Custom format "dddd, dd mmmm, yyyy".
Cell "A3" contains the text string "20200523".
Cell "A4" converts the text string in cell "A3" to its corresponding date serial number. Custom format "dddd, dd mmmm, yyyy".
Cell "A5" contains the text string "20200208".
Cell "A6" converts the text string in cell "A5" to its corresponding date serial number. Custom format "dddd, dd mmmm, yyyy".

 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

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 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 serial number representing today's date.


Related Formulas

Return a date in US date format


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