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

Screenshot 2022-07-27 at 11.23.05 AM.png

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.

Find more posts tagged with

Accepted answers

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

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.