Formula Help - Automating Quarters Based on Date

Hi there,

I'm trying to create a formula that will automatically pull Q1, Q2, Q3, or Q4 depending on the finish date of an activity. I worked with someone from the ProDesk, and he showed me how to create this using the normal quarters of the year. The problem is that my company's fiscal year is a bit different, with Q1 starting in February. Here is my thinking:

Q1 - Feb, March, April (<5 and >1)

Q2 - May, June, July (<8 and >4)

Q3 - August, September, October (<11 and >7)

Q4 - November, December, January (Otherwise)

Can someone help me figure out the correct formula? I keep getting the unparseable error. Here is my starting point:

=IF(MONTH([*Finish]@row) < 5 AND(MONTH([*Finish]@row > 1) "Q1", IF(MONTH([*Finish]@row) < 8, AND(MONTH([*Finish]@row > 4) "Q2", IF(MONTH([*Finish]@row) < 11 AND(MONTH([*Finish]@row > 7) "Q3", "Q4"))))))

Thanks so much!!!

Find more posts tagged with

Formulas

Comments

Nick Burrus

You are missing a comma after the 5 and the 11.

Thanks,

Nick

Stria LLC Signature

Lchicklis

I think a couple things were wrong (i.e. there should not be closed parentheses after the 1, 4, and 7; they should go after "row")... I'm closer now but still not quite there yet:

=IF(MONTH([*Finish]@row) < 5 AND(MONTH([*Finish]@row) > 1, "Q1", IF(MONTH([*Finish]@row) < 8 AND(MONTH([*Finish]@row) > 4, "Q2", IF(MONTH([*Finish]@row) < 11 AND(MONTH([*Finish]@row) > 7, "Q3", "Q4"))))))

I've tried with commas after 5, 8, and 11, and without.

Paul Newcome

Your AND functions are not being used correctly. Try this instead.

=IF(AND(MONTH([*Finish]@row) < 5, MONTH([*Finish]@row) > 1), "Q1", IF(AND(MONTH([*Finish]@row) < 8, MONTH([*Finish]@row) > 4), "Q2", IF(AND(MONTH([*Finish]@row) < 11, MONTH([*Finish]@row) > 7), "Q3", "Q4"))

When incorporating an AND function, the proper syntax is:

AND(logical_statement_1, logical_statement_2)

When used in the logical statement portion of an IF statement, it reads something like this

=IF(AND(this is true, this is also true), then do this, else do that)

Lchicklis

Thank you so much!!! I really appreciate it

Paul Newcome

Happy to help!

Sign in to join the conversation!

Quick Links

Trending discussions

Calculating Quarters with a Start and a Finish date
I searched the community and did not see a formula that calculated Quarter based on a Start and a Finish date while also accounting for cases where the year for the Start and Finish date differ. The formula below handles that. A few notes … It assumes that Finish is always >= Start. The entire formula is wrapped with…
Circular Reference with formula
i am not good with writing formulas within a sheet. I asked the AI tool in the sheet to help me write a formula and I am getting a Circular reference back. I'm trying to have it write the following formula for me. If the column "Tracking #" is not blank, change Column "Deployment Phase" to "Shipped" I have tried adding in…
How to delay the execution of a workflow whose trigger occurred much earlier?
I have a workflow that detects a row(date) that is changing its Fiscal Week Finish Date and send this row to other Sheet. Long story short: I collect delayed tasks in separate file & I tag reasons for delay for these tasks. It happens that plan are analyzed 2-3 hours and dates are being changed several times. I would like…