Converting a five digit number to date format in Excel?

microsoft-excel worksheet-function date-time

5,406

This formula will extract the first four digits as the year, the second two as the month and use a 1 for the day. You can do a Paste Special Values, then delete the original original column if you only want the new result.

=DATE(MID(B1,1,4),MID(B1,5,2),1)

5,406

AgainstClint

Starting off at my first System Administrators job, and i'm sure I will need help in the near future.

Updated on September 17, 2022

Comments

AgainstClint over 1 year
In these spread sheets I have an entire column of values that are supposed to be dates, such as.
```
200701 = 1/1/2007
200702 = 2/1/2007
200905 = 5/1/2009
```
And so on and so forth.

These will all be in column B (if that matters) and I was curious if there is a simple way to convert these into the desired date without doing a search and replace. Some of these spreadsheets are thousands upon thousands of pages long and it will be very time consuming.
- Linker3000 over 13 years
  
  Are there no digits for the month - ie: are they all month '1'?
- AgainstClint over 13 years
  
  Yeah, just having a "1" for the date is fine it seems, they are just more concerned with the month.
- Dennis Williamson over 13 years
  
  @Linker3000: The resulting dates are in MM/DD/YYYY form rather than DD/MM/YYYY.
AgainstClint over 13 years

Works like a charm. Anyway you could explain on why this works, out of my own curiosity?
Dennis Williamson over 13 years

The DATE formula takes (YEAR,MONTH,DAY) as arguments and converts them to a datetime value. The MID formula takes (ADDRESS,START-CHAR,CHAR-COUNT) as arguments so (MID(B1,1,4) says to extract four characters starting at the first and MID(B1,5,2) says to extract two starting at the fifth.
Dennis Williamson over 13 years

@AgainstClint: I forgot to prefix that comment with "@AgainstClint".
AgainstClint over 13 years

Also, just a weird heads up, if you put the data into a Pivot Table, it will convery the five digit number into the correct date and format. Not exactly sure why that works, but it is kind of interesting.
Sux2Lose over 13 years

@Dennis: Wondering why you have a preference for the Mid function as opposed to the Left and Right function in this particular case. Just curious. Thanks.
Dennis Williamson over 13 years

@Sux2Lose: They would work equally well, but I like the fact that the end of one (4) precedes the beginning of the next (5) for readability and that if you were also extracting the day-of-month you'd need to use MID for one field anyway.