Excel leading zero for day and month

Frank C · Mar 22, 2012

Hi
In Excel, I want to get a leading zero on a numeric day and month entries for sort.
I have separate columns for year, month and day.
I have tried many formats without success. If I format as text with a leading zero I get a warning that a number is stored as text and the sort is incorrect.
Does anyone have a suggestion?
Thanks
Frank C

Lottiemansion · Mar 23, 2012

Hi,

You can use a custom format & from the dd/mm/yyyy format use only the dd part.

When you insert a date you will get the first two digits for the day. It is the same for a month (mm) & year (yyyy) or (yy)

i.e 23/03/2012 = 23
or 01/03/2012 = 01

OK?

Frank C · Mar 23, 2012

Thanks Lottie
I tried your suggestion with no success. I don't understand the custom approach. I also tried formating the date in one column as dd/mm/yyyy. That did not sort properly either.
Frank C

Lottiemansion · Mar 23, 2012

Hi,

Please post a picture of the excel spread sheet & explain the cell contents & cell formating.

If possible/ appropriate post the file & I will look at it. This will enable me to advise further. OK?

See if these files help you to solve the problem? Please watch the video first!

Frank C · Mar 24, 2012

Thanks Lottie,
I reluctantly changed to English US format MM/DD/YYYY in one column that meant that I had to transpose the month and day. That column DID sort properly. No leading zeros on month or day I will live with that solution.
When I get some time I will try the English Canadian format with the entire date in one column (YYYY-MM-DD) this makes more sense to me than the English US format.
Frank C

Lottiemansion · Mar 24, 2012

OK,

Glad you have made some progress & I wish you well in your ventures.

Paul H.

Excel leading zero for day and month

Frank C

My Computer

System One

Lottiemansion

My Computer

System One

Frank C

My Computer

System One

Lottiemansion

My Computer

System One

Frank C

My Computer

System One

Lottiemansion

My Computer

System One