Day or month as 2 digits

ginnylin

Hi all - I want to use the day and month of people's birthdates to create user IDs.  I can extract the month easily enough, but I want all of these to be 2 digits (ie. if it's June, I can extract '6' but I want '06').

Does anyone know how to get a 2 digit month?

Thanks

 

SmSulli

Hi, Here is a formula that will force a 0 on to single digit months.  But is will make the number text.  So this may not be helpful.  You didn't specify the column names, so you'll need to change that in the formula.  Give it a try and let us know if this helps, or if you need something else.  Is there a reason why you need a 2-digit number?

=IF(Date1 = "", "", IF(MONTH(Date1) > 9, MONTH(Date1), "0" + MONTH(Date1)))

Shawn

J. Craig Williams

I suspect padding digits because it will end up in a USER ID.

In this case then, having the return be Text will be OK.

Here's the extrapolation with the day are part of the ID

=IF(Date1 = "", "", IF(MONTH(Date1) > 9, "" + MONTH(Date1), "0" + MONTH(Date1))) + IF(Date1 = "", "", IF(DAY(Date1) > 9, "" + DAY(Date1), "0" + DAY(Date1)))

Note that all return possibilities are now Text, not mixed Text and Number.

I hope this helps.

Craig

H. Obringer

This doesn't work for me. I still need the numbers. Is there a reason we wouldn't want to be able to sort the month numerically? Why doesn't the formula return a two digit month by default? Is there another formula we can use to add leading 0 to numbers?