How to work on a formula to display 2 digit value?

Options

With reference to the above, we are trying to create a Serial Number with a few combination of data. One of it is the month and year submitted.

I am using the MONTH(@cell) and YEAR(@cell) formula (as shown in the screenshot) to obtain the month and year.

Is there any formula to convert it into a 2 digit value as follows?

Month: 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12

Year: 22 for 2022, 23 for 2023 ...

Appreciate assistance.

Best Answer

  • Kevin St-Pierre
    Kevin St-Pierre ✭✭✭
    Answer ✓
    Options

    You could try the following,

    For Month column:

    =RIGHT(100 + MONTH([Date Submitted]@row), 2)

    This will add 1 at the beginning of the number, and then strip the first character, so this way it will trick the sheet into displaying 2 digit long including the leading 0.


    For the Year column:

    =RIGHT(YEAR([Date Submitted]@row), 2)

    This should keep the last 2 digits of the 4-digit year.


    Kevin

Answers

  • Kevin St-Pierre
    Kevin St-Pierre ✭✭✭
    Answer ✓
    Options

    You could try the following,

    For Month column:

    =RIGHT(100 + MONTH([Date Submitted]@row), 2)

    This will add 1 at the beginning of the number, and then strip the first character, so this way it will trick the sheet into displaying 2 digit long including the leading 0.


    For the Year column:

    =RIGHT(YEAR([Date Submitted]@row), 2)

    This should keep the last 2 digits of the 4-digit year.


    Kevin

  • Vivien Chong
    Vivien Chong ✭✭✭✭✭✭
    Options

    thanks @Kevin St-Pierre . The formula works. I know it can be done. Just don't know how.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!