Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Day or month as 2 digits

Options
ginnylin
ginnylin
edited 12/09/19 in Archived 2017 Posts

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

 

Comments

  • SmSulli
    Options

    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
    J. Craig Williams ✭✭✭✭✭✭
    Options

    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
    H. Obringer ✭✭✭
    Options

    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?

This discussion has been closed.