Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 3 down vote accepted

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

Please see: <-- You can do it using custom formats.

share|improve this answer
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 Mar 10 '11 at 14:35
@Dave Mackey, I have updated my answer to be more precise. – eradicus Mar 10 '11 at 14:47

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.

share|improve this answer

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


Does this do what you want?

share|improve this answer

You must log in to answer this question.

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