Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

3 Answers 3

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: http://support.microsoft.com/kb/81518 <-- 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
1  
@Dave Mackey, I have updated my answer to be more precise. –  Joset 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

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

Does this do what you want?

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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