Convert Text In YYYYMMDD Format To Date Format

**S M C** · 05-16-2011, 03:38 PM

If A1 contains text in YYYYMMDD format, use

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

**Rick Rothstein** · 02-28-2012, 12:04 AM

Originally Posted by S M C

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