If A1 contains text in YYYYMMDD format, use
=DATEVALUE(TEXT(A1,"0000\/00\/00"))
Printable View
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...Quote:
Originally Posted by S M C
=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