adding zeros to dates

zebra7860

Active Member
Joined
Jan 9, 2004
Messages
393
Hello,

I have dates in the following format in an Excel column:

MM/DD/YYYY

the YYYY is not a problem but the months of January(01) to Sep(09) and the days from 1 to 9 are a problem in that I need to add a ZERO in front of the single digit to make them a double digit.

For example,

1/3/1950 should be 01/03/1950

7/9/2003 should be 07/09/2003

How do I go about adding ZERO's in single digits of months and days to make them DOUBLE digits?

Any help would be appreciated,

Thanks,
Al :)
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

zebra7860

Active Member
Joined
Jan 9, 2004
Messages
393
Okay, I know how to go to format cells/custom but I see no format in the form of MM/DD/YYYY. I do see MM/DD/YY however.

Could someone also please tell me what to do from there. I know it sounds silly but I highlighted the entire column of dates and clicked on various format codes but nothing effected the highlighted column. What steps are required to change the highlighted column?

Thanks to everybody!
Al
 

Seti

Well-known Member
Joined
May 19, 2002
Messages
2,916

ADVERTISEMENT

If you go to Format, then choose custom (the last choice in the category box), in the type window put mm/dd/yyyy. This should change the format to have leading zeroes. If it doesn't, look at what is in a sample cell and post back. Is it a formula or a value?
 

zebra7860

Active Member
Joined
Jan 9, 2004
Messages
393
I went to format|number|custom and typed in mm/dd/yyyy and prior to that I highlighted the dates column. Nothing changed in the date column.

The format of mm/dd/yyyy is now in the 'type" window but nothing gets changed in the dates column, and the question is why??

Can anybody figure this out?

Al
 

Iridium

Well-known Member
Joined
Jul 15, 2002
Messages
2,831

ADVERTISEMENT

You could change the 'Regional and Language options' setting for the 'Short date:' format in the control panel to mm/dd/yyyy perhaps?

(using XP)

HTH
 

Seti

Well-known Member
Joined
May 19, 2002
Messages
2,916
What exactly is in a sample cell? Would you hit F2, then highlight everything in the formula bar, then paste this into a reply to the board? That way, someone might spot something funny.
 

zebra7860

Active Member
Joined
Jan 9, 2004
Messages
393
funny thing is that when I choose the MM/DD/YYYY format and then highlight the dates column, nothing happens as I said but when I individually choose a date, double click and then go to the next date, it does change the previous date to the MM/DD/YYYY format!!

This would be too cumbersome to change the dates one at a time.

Al
 

2rrs

Active Member
Joined
Dec 25, 2002
Messages
346
Highlight the dates col and then choose the format.

"funny thing is that when I choose the MM/DD/YYYY format and then highlight the dates column, nothing happens "
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,914
Members
413,952
Latest member
JGer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top