Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
Are there no digits for the month - ie: are they all month '1'? –  Linker3000 Oct 1 '10 at 14:41
    
Yeah, just having a "1" for the date is fine it seems, they are just more concerned with the month. –  AgainstClint Oct 1 '10 at 14:43
1  
@Linker3000: The resulting dates are in MM/DD/YYYY form rather than DD/MM/YYYY. –  Dennis Williamson Oct 1 '10 at 15:04
add comment

1 Answer

up vote 2 down vote accepted

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)
share|improve this answer
    
Works like a charm. Anyway you could explain on why this works, out of my own curiosity? –  AgainstClint Oct 1 '10 at 14:59
1  
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 Oct 1 '10 at 15:02
    
@AgainstClint: I forgot to prefix that comment with "@AgainstClint". –  Dennis Williamson Oct 1 '10 at 15:14
    
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. –  AgainstClint Oct 1 '10 at 15:25
    
@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. –  Sux2Lose Oct 1 '10 at 15:40
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.