PDA

View Full Version : 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
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