up vote 1 down vote favorite
share [g+] share [fb]

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.

link|improve this question
80% accept rate
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
feedback

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)
link|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
feedback

Your Answer

 
or
required, but never shown
discard

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