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

Vivien Chong

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.

Kevin St-Pierre

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

Kevin St-Pierre

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

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