Formula for quarter based on finish date

Becca Grube · 01/14/21

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".

JeremiahHorstick · 01/14/21

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"))))

Becca Grube · 01/14/21

@JeremiahHorstick, how do I combine the two formulas?

JeremiahHorstick · 01/14/21

@Becca Grube

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"))))

Paul Newcome · 01/14/21

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")))

L_123 · 01/14/21

="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

Becca Grube · 01/14/21

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")))

Paul Newcome · 01/14/21

@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.

L_123 · 01/14/21

@Paul Newcome NP. I actually couldn't remember exactly how I did it either and had to go back and look haha

Paul Newcome · 01/14/21

@L@123 Hahaha. Well I'm glad you did.

JeremiahHorstick · 01/16/21

@L@123 Nice!

Lchicklis · 05/24/21

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!

Paul Newcome · 05/24/21

@Lchicklis What is your application? Are you wanting to output "Q#" based on a specific date?

Lchicklis · 05/25/21

@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.

JeremiahHorstick · 05/25/21

=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.

L_123 · 05/25/21

I think you could do something like

="Q" + if(month([finish date]@row) = 1,4,int((month([Finish Date]@row) + 1) / 3))

Formula for quarter based on finish date

Answers

Help Article Resources

Categories