Formula Help - Automating Quarters Based on Date

Lchicklis

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!!!

 

Nick Burrus

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

Thanks,

Nick

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!