Convert Text In YYYYMMDD Format To Date Format

[Rick Rothstein]

If A1 contains text in YYYYMMDD format, use

=DATEVALUE(TEXT(A1,"0000\/00\/00"))

You can shorten that formula by eliminating the DATEVALUE function call and simply involving the text representation of the date in a mathematical calculation that does not change its underlying value. All of these return the same date value as your posted formula...

=1*TEXT(A1,"0000\/00\/00")

=0+TEXT(A1,"0000\/00\/00")

=--TEXT(A1,"0000\/00\/00")

=TEXT(A1,"0000\/00\/00")^1

In addition, you can save another two characters by using a dash instead of an "escaped" slash. These all work as well...

=1*TEXT(A1,"0000-00-00")

=0+TEXT(A1,"0000-00-00")

=--TEXT(A1,"0000-00-00")

=TEXT(A1,"0000-00-00")^1