How to convert Date (MM/DD/YYYY) into Month and Quarters and Year such as "Q1 2020" and "Jan 2020"
How to convert Date (MM/DD/YYYY) into Month and Quarters and Year such as "Q1 2020" and "Jan 2020"
Best Answer
-
=IF(MONTH(ED2) = 1, "JAN ", IF(MONTH(ED2) = 2, "FEB ", IF(MONTH(ED2) = 3, "MAR ", IF(MONTH(ED2) = 4, "APR ", IF(MONTH(ED2) = 5, "MAY ", IF(MONTH(ED2) = 6, "JUN ", IF(MONTH(ED2) = 7, "JUL ", IF(MONTH(ED2) = 8, "AUG ", IF(MONTH(ED2) = 9, "SEP ", IF(MONTH(ED2) = 9, "OCT ", IF(MONTH(ED2) = 11, "NOV ", IF(MONTH(ED2) = 12, "DEC ")))))))))) + YEAR((ED2))))
This one worked
Answers
-
To convert to "Q# yyyy" you would use something along the lines of...
="Q" + IF(MONTH([Date Column]@row) >= 10, "4 ", IF(MONTH([Date Column]@row) >=7, "3 ", IF(MONTH([Date Column]@row) >=4, "2 ", "1 "))) + YEAR([Date Column]@row)
To convert to "Mon yyyy", you could either build out a table where the month number is listed in one column and the desired month text is in another and then use an INDEX/MATCH or VLOOKUP along the lines of
=INDEX([Month Text]:[Month Text], MATCH(MONTH([Date Column]@row), [Month Number]:[Month Number], 0))
or you could use a nested IF statement for each month such as
=IF(MONTH([Date Column]@row) = 1, "Jan ", IF(MONTH([Date Column]@row) = 2, "Feb ", ................................................................................................))))))))))) + YEAR([Date Column]@row)
-
=IF(MONTH(ED2) = 1, "JAN ", IF(MONTH(ED2) = 2, "FEB ", IF(MONTH(ED2) = 3, "MAR ", IF(MONTH(ED2) = 4, "APR ", IF(MONTH(ED2) = 5, "MAY ", IF(MONTH(ED2) = 6, "JUN ", IF(MONTH(ED2) = 7, "JUL ", IF(MONTH(ED2) = 8, "AUG ", IF(MONTH(ED2) = 9, "SEP ", IF(MONTH(ED2) = 9, "OCT ", IF(MONTH(ED2) = 11, "NOV ", IF(MONTH(ED2) = 12, "DEC ")))))))))) + YEAR((ED2))))
This one worked
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.2K Get Help
- 386 Global Discussions
- 212 Industry Talk
- 445 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 291 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!