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?
Best Answer
-
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
Answers
-
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
-
That works, thanks so much!
-
You're welcome!
-
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}))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!