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() = Friday 29 November 2024
2	=NOW() = Friday 29 November 2024 11:44:05
3	=TEXT(A1, "yyyymmdd") = 20241129
4	=DATEVALUE(RIGHT(A3, 2)&"/"&MID(A3, 5, 2)&"/"&(LEFT(A3, 4))) = Friday, 29 November, 2024
5	20200523
6	=DATEVALUE(RIGHT(A5, 2)&"/"&MID(A5, 5, 2)&"/"&(LEFT(A5, 4))) = Saturday, 23 May, 2020
7	20200208
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
1	20200715
2	=DATEVALUE(MID(A1,5,2)&"/"&RIGHT(A1,2)&"/"&(LEFT(A1,4))) = Wednesday, 15 July, 2020
3	20200523
4	=DATEVALUE(MID(A3,5,2)&"/"&RIGHT(A3,2)&"/"&(LEFT(A3,4))) = Saturday, 23 May, 2020
5	20200208
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