How to Convert YYYYMMDD Text Value to Date Format in Excel

There may be times when you receive a file or export data that contains dates that are in text format and in YYYYMMDD format.  If you simply try to convert this to Date format in Excel, you'll get the ########## error.

To convert the text YYYYMMDD to date, do the following:

  1. In a new column other than the one that contains your original data, enter the formula =DATE(LEFT(B5,4),MID(B5,5,2),RIGHT(B51,2)) (This example assumes the data begins at B5.  Make sure you leave the cell references as relative (i.e., don't use the $B$5 absolute cell reference format).
  2. Copy that formula down the entire range, either by copying and doing CTRL+D or using the fill bar (the little black square on the lower right corner of a cell highlight box).
  3. You should now have your values.  You can either now hide the original column or copy over it by doing a copy and paste special...values command.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.