How can I convert non-standard date data in Excel?

Question

For some silly reason the data I've been provided has dates in the following format: mmddyyyy but if the date is something beginning with zero (e.g. 03132010) then it shows up without the leading zero. I need to update all of these dates so that if they don't already have two mm fields they have a 0 added to the beginning.

Answer 1

You can highlight the cells concerned, right click then format cells. You can select how the cells will handle formats.

Please see: http://support.microsoft.com/kb/81518 <-- You can do it using custom formats.

Answer 2

The way I'd try it is somewhat convoluted, but I tested it and it works.

Make a copy of your sheet. Add five columns after the date. In the first column of the new ones, use the formula if(length(A1=8);left(A1;2);left(A1;1)) (assuming that your date is in cell A1). In the second one, use right(A1;6). In the third, use left(c1;2). In the fourth, use right(c1;4).

To get the date in the fifth column, formatted as a date, use date(e1;d1;b1).

Actual formula names may vary, I backtranslated them from German.

Answer 3

I got it to work with this formula in an extra column that pre-pends a zero. Some assumptions:

1. your original value is in cell A2

2. the format of all cells is "General"

3. the month is the only part of the date that would NOT have a leading zero

=IF(LEN(A2)<>8,"0"&A2,A2)

Does this do what you want?