Results 1 to 2 of 2

Thread: Convert Text In YYYYMMDD Format To Date Format

  1. #1
    Grand Master
    Join Date
    Apr 2011
    Posts
    22
    Rep Power
    10

    Convert Text In YYYYMMDD Format To Date Format

    If A1 contains text in YYYYMMDD format, use

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

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by S M C View Post
    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
    Last edited by Rick Rothstein; 02-28-2012 at 12:10 AM.

Similar Threads

  1. Replies: 3
    Last Post: 04-05-2013, 08:24 AM
  2. date format
    By Safal Shrestha in forum Excel Help
    Replies: 1
    Last Post: 01-02-2013, 02:53 PM
  3. How To Change Date Format in Excel
    By Oh!Calcutta in forum Excel Help
    Replies: 1
    Last Post: 11-01-2012, 09:36 PM
  4. Default Cell Format Changed to Date
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 05-07-2012, 10:14 PM
  5. Replies: 4
    Last Post: 03-10-2012, 07:15 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •