Formula to add leading zero to months 1-9 of the year only.

I need to add leading zeros to month numbers Jan-Sept only as sorting by month number does not work due to Months Oct-Dec starting with number 1. If I sort by month number, it comes up like this.

1 (Jan), 10 (Oct), 11 (Nov), 12 (Dec), 2 (Feb), 3(March) ...

Does anyone have a formula for this?

Find more posts tagged with

Formulas

Support

functionality

Workflows in Smartsheet

Accepted answers

John_Foster

If the month field is a text value try adding a zero in front of the value and then taking the right two digits using the formula below.

=RIGHT("0" + {YOUR_MONTH_VALUE_HERE}, 2)

Hope it helps!

John

All comments

John_Foster

If the month field is a text value try adding a zero in front of the value and then taking the right two digits using the formula below.

=RIGHT("0" + {YOUR_MONTH_VALUE_HERE}, 2)

Hope it helps!

John

Reinierh

That works, thanks so much!

John_Foster

You're welcome!

Jonathan Gann

I typically add leading zeros using the LEN() function to see if a number needs it, nested into IF()s.

e.g.

=IF(LEN(MONTH({date}))=1,"0"+MONTH({date}),MONTH({date}))

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.