Formula for quarter based on finish date
I want to create a formula that will auto-populate the Quarter column with Q1, Q2, Q3, or Q4 based on the date entered in the "Finish Date" column. For instance, if the "Finish Date" is 4/15/2021, I want the Quarter to be "Q2".
Answers
-
You could combine these two formulas or use them. =
= MONTH({the date})
=IF(month <= 3, "Q1", IF(month <= 6, "Q2"2, IF(month <= 9, "Q3", IF(month <= 12, "Q4"))))
-
@JeremiahHorstick, how do I combine the two formulas?
-
Try this. I'm not sure the syntax is perfect.
=IF(MONTH([Finish Date]@row <= 3, "Q1", IF(MONTH([Finish Date]@row <= 6, "Q2"2, IF(MONTH([Finish Date]@row <= 9, "Q3", IF(MONTH([Finish Date]@row <= 12, "Q4"))))
-
Try this one...
=IF(MONTH([Finish Date]@row) <= 3, "Q1", IF(MONTH([Finish Date]@row) <= 6, "Q2", IF(MONTH([Finish Date]@row) <= 9, "Q3", "Q4")))
-
="Q"+ int((month([Finish Date]@row) + 2) / 3)
Little bit shorter if you use math instead of conditionals. This is among the date formulas I have published in a comment below.
https://community.smartsheet.com/discussion/68494/list-of-date-formula-i-have-used-repeatedly#latest
-
Thank you, all! This one worked like a charm: =IF(MONTH([Finish Date]@row) <= 3, "Q1", IF(MONTH([Finish Date]@row) <= 6, "Q2", IF(MONTH([Finish Date]@row) <= 9, "Q3", "Q4")))
-
@L@123 Thanks for that. I know there was a way to get it to work using the INT function, but was drawing a blank on it.
-
@Paul Newcome NP. I actually couldn't remember exactly how I did it either and had to go back and look haha
-
@L@123 Hahaha. Well I'm glad you did.
-
@L@123 Nice!
-
Would someone be able to help with a quarter formula where Q1 = Feb-April, Q2 = May-July, Q3 = August-Oct, and Q4 = Nov-Jan?
Thanks so much!
-
@Lchicklis What is your application? Are you wanting to output "Q#" based on a specific date?
-
@Paul Newcome this was the full question from a colleague:
We are needing to pull in a created and completed metric by month and quarter in our overall dashboard.
In our main sheet we have an intake (created) and completed date column. Do you know how we would could auto populate a column indicating what month and quarter it is in order to create a dashboard metric?
I believe it would be same as the application above but with Q1 starting in Feb.
-
=IF(OR(month@row = 2, month@row = 3, month@row = 4), 1, IF(OR(month@row = 5, month@row = 6, month@row = 7), 2, IF(OR(month@row = 8, month@row = 9, month@row = 10), 3, IF(OR(month@row = 11, month@row = 12, month@row = 1), 4))))
You could nest the MONTH function in the formula too. In this example I have the month value in a row.
-
I think you could do something like
="Q" + if(month([finish date]@row) = 1,4,int((month([Finish Date]@row) + 1) / 3))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!