Tag : date

Convert 7 or 8 character string into excel date

Today my wife asked me to fix one of her spreadsheets. One column contained a date encoded in either 7 or 8 characters in the format DMMYYYY or DDMMYYYY, but she needed the data as actual dates.

Here's the quick formula for this (assuming Y2 contains the date string):

=IF(LEN(Y2)=7,
             DATE(MID(Y2,4,4),MID(Y2,1,1),MID(Y2,2,2)),
             DATE(MID(Y2,5,4),MID(Y2,1,2),MID(Y2,3,2)))