Leading Zeros for Dates in Excel

Dell / INSPIRION
March 4, 2009 at 07:35:58
Specs: Windows XP, 1.0 Gb
I need to divide a date which is in one cell into three cells. As an example, for 01/12/09, I used =left(month(<cell>),2) which returned "1"; =(day(<cell>)) which returned "9" and =right(year(<cell>,2) which returned "09". However, each date needs to be two characters in length, i.e., "1" needs to be "01".

I tried different IF statements with LEN but couldn't get the right syntax.


See More: Leading Zeros for Dates in Excel

Report •


#1
March 4, 2009 at 09:19:39
Custom Format cells as Zero Zero ( 00 )

On the tool bar:

Format
Cells
Custom

In the box under "Type:"
enter 00

Press OK

Also you do not need the left function for Month
simply do =MONTH(A1)
The same applies to Day, simply =DAY(A1)

You do not need the YEAR, just =RIGHT(A1,2) will get you
the year

MIKE


Report •

#2
March 4, 2009 at 09:24:13
Correction:

You DO need the YEAR, your original formula is correct.
=RIGHT(YEAR(A1),2) get you the year.

MIKE


Report •

#3
March 4, 2009 at 10:00:18
Thank you for your quick response; it worked perfectly.

Report •

Related Solutions

#4
March 4, 2009 at 10:15:36
Just out of curiosity, what are you planning to do once you have your date split up?
Some type of Date Math?

MIKE


Report •

#5
March 4, 2009 at 10:20:09
The original form for this data used one cell for the date; the revised one uses three: MM DD YY.

The original form is linked to an input form. On that form, the date still uses one cell but the revised output form uses three cells for the date.


Report •

#6
March 4, 2009 at 10:47:46
Ok, makes sense. Thanks for indulging my curiosity.

MIKE


Report •


Ask Question