1

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.

Improve this question

3 Answers 3

Reset to default
3

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.

Improve this answer
2
  • Unfortunately, there doesn't appear to be any option to format the date in this manner, and selecting any other format results in all those without a leading 0 to become ######
    – Dave Mackey
    Commented Mar 10, 2011 at 14:35
  • 1
    @Dave Mackey, I have updated my answer to be more precise.
    – setzamora
    Commented Mar 10, 2011 at 14:47
1

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.

Improve this answer
0

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?

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .