Separating out a date by Quarter and Year
Hello,
I have an End Date column (which is formatted as mm/dd/yyyy). I need a formula to show as Q1-2022 (example) for any given date in the column.
In excel, I was able to find a formula that works but transferring it to Smartsheet I receive an UNPARSEABLE error message. That formula is: ="Q"&INT((MONTH(End Date)+2)/3)&YEAR(End Date)
I believe one of my issues is Smartsheet uses + instead of & but from there I feel lost (probably trying to figure it out for too long now). Any help is greatly appreciated.
Thank you in advance
Best Answer
-
That worked!! Thank you!!
Answers
-
Consider if then logic as per below:
-
Hello,
Thank you for the suggestion.
This one works but it only gives me which Quarter: =IF(MONTH([End Date]@row) <= 3, "Q1", IF(MONTH([End Date]@row) <= 6, "Q2", IF(MONTH([End Date]@row) <= 9, "Q3", "Q4")))
I read that + is used to connect for Smartsheet instead of & but I get UNPARSEABLE when I tried to add it:
=IF(MONTH([End Date]@row) <= 3, "Q1" + YEAR([End Date], IF(MONTH([End Date]@row) <= 6, "Q2" =YEAR([End Date], IF(MONTH([End Date]@row) <= 9, "Q3" + YEAR([End Date], "Q4" + YEAR([End Date])))
I feel like I am close!!!
-
Hi @NJohnson
You did have the detail but needed to take care of the Quarter calculation before adding the year piece on after that was worked out.
The below should work
=IF(MONTH([End date]@row) <= 3, "Q1", IF(MONTH([End date]@row) <= 6, "Q2", IF(MONTH([End date]@row) <= 9, "Q3", "Q4"))) + "-" + YEAR([End date]@row)
Hope that helps
Thanks
Paul
-
That worked!! Thank you!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!