How can I get the month text

Based on a date in a cell, how can I easily calculate (in another column) the month text (such as "Jan" or "Feb"). In Excel I would use the TEXT function, with a format of "MMM").
Adrian Mandile
CHESS Consulting Australia - Smartsheet Solution Provider Gold Partner
Collaborative | Holistic | Effective | Systems | Solutions
Best Answer
-
For three digit month names you can avoid nasty nested IFs by using a much simpler formula:
=MID("JanFebMarAprMayJunJulAugSepOctNovDec", (Mth@row * 3) - 2, 3)
Answers
-
You can create a Month column, make it formula =month(date@row) this will give you the month number.
Then there are two ways to solve the text portion.
First: Create a nested if statement for each month, if(month@row=1, "Jan", if(month@row=2, "Feb", etc.
Second: create a new sheet that has numbers 1 -12 in one column, and months jan, feb, mar, etc in another. Then use an index(text month, match(Month@row, new sheet number column, 0))
Hope this helps.
best,
Brad
www.MVPOPS.com
-
Hi @Adrian @ Chess,
I hope you're well and safe!
To add to MVP's excellent advice/answer.
Here's the entire formula for option 1.
= IF(Month@row <> "", IF(MONTH(Month@row) = 1, "01 January", IF(MONTH(Month@row) = 2, "02 February", IF(MONTH(Month@row) = 3, "03 March", IF(MONTH(Month@row) = 4, "04 April", IF(MONTH(Month@row) = 5, "05 May", IF(MONTH(Month@row) = 6, "06 June", IF(MONTH(Month@row) = 7, "07 July", IF(MONTH(Month@row) = 8, "08 August", IF(MONTH(Month@row) = 9, "09 September", IF(MONTH(Month@row) = 10, " 10 October", IF(MONTH(Month@row) = 11, "11 November", IF(MONTH(Month@row) = 12, "12 December")
Did that work/help?
I hope that helps!
Be safe and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
I found this looking for the same assist. However, it did not work as intended and searched some more. I finally have it worked out and thought I would share.
This helped with polling the month number out for me and works for the entire calendar year with no modifications needed.
=IF(ISBLANK([Date Column Name]@row), "", MONTH([Date Column Name]@row))
-
For three digit month names you can avoid nasty nested IFs by using a much simpler formula:
=MID("JanFebMarAprMayJunJulAugSepOctNovDec", (Mth@row * 3) - 2, 3)
-
By the same logic:
=MID("SunMonTueWedThuFriSat", (Day@row) * 3 - 2, 3)
-
Thanks! These last two responses were very inventive!
I was looking to prompt Smartsheet for a CHOOSE() function or a TEXT() custom formatting function.
Adrian Mandile
CHESS Consulting Australia - Smartsheet Solution Provider Gold Partner
Collaborative | Holistic | Effective | Systems | Solutions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 433 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 506 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!