2 Formulas Required
I'm trying to find a formula that will calculate the financial quarter based on the file review date
I have used numerous formulas from the community but none appear to work. This is the one that's closest however, as you will see from the screen shot the quarters aren't calculating correctly
=IFERROR(IF(MONTH([Date of Review]@row) > 3, 1, IF(MONTH([Date of Review]@row) < 7, 2, IF(MONTH([Date of Review]@row) < 10, 3, IF(MONTH([Date of Review]@row) < 4, 4)))), " ")
Jan  Mar = Q4
Apr  Jun = Q1
Jul  Sept = Q2
Oct  Dec = Q4
Second formula is an IF(AND needed to work out network days based on the File Review Date and the Corrective Actions Timeline, to work out a due date e.g.
If File Review Date was Friday 9th April and the Corrective Action Timeline = "Immediately" the Corrective Action Due Date would be Monday 12th April
If File Review Date was Monday 5th April and Corrective Action Timeline was "5 Working Days", the Corrective Action Due Date would be Friday 9th April
If File Review Date was Monday 5th April and Corrective Action Timeline was "10 Working Days", the Corrective Action Due Date would be Friday 16th April
TIA
Best Answers

Hi @Cheryl Collins
Hope you are fine, the following is the first formula to calculate the quarter:
=IF(MONTH([Date Of Review]@row) > 9, 4, IF(MONTH([Date Of Review]@row) <= 3, 1, IF(AND(MONTH([Date Of Review]@row) > 3, MONTH([Date Of Review]@row) <= 6), 2, IF(AND(MONTH([Date Of Review]@row) > 6, MONTH([Date Of Review]@row) <= 9), 3))))
the result in the following screenshot
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Hi @Cheryl Collins
Hope you are fine, for the Second formula for Corrective Actions Timeline you need to create the following helper columns with those formulas:
1 Weekday =WEEKDAY([Date Of Review]@row)
2 Actual Days=IF(AND(Weekday@row = 6, [Corrective Action Timeline]@row = "Immediately"), 3, IF(AND(Weekday@row = 6, [Corrective Action Timeline]@row = "5 Working Days"), 6, IF(AND(Weekday@row = 6, [Corrective Action Timeline]@row = "10 Working Days"), 13, IF(AND([Corrective Action Timeline]@row = "Immediately", Weekday@row < 6), 0, IF(AND([Corrective Action Timeline]@row = "5 Working Days", Weekday@row < 6, Weekday@row <> 2), 6, IF(AND([Corrective Action Timeline]@row = "10 Working Days", Weekday@row < 6, Weekday@row <> 2), 13, IF(AND([Corrective Action Timeline]@row = "5 Working Days", Weekday@row = 2), 4, IF(AND([Corrective Action Timeline]@row = "10 Working Days", Weekday@row = 2), 11))))))))
3 Corrective Action Due Date =[Date Of Review]@row + [Actual Days]@row
the following screen shot show the results
you can test the formulas using the following Published Link
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Answers

Hi @Cheryl Collins
Hope you are fine, the following is the first formula to calculate the quarter:
=IF(MONTH([Date Of Review]@row) > 9, 4, IF(MONTH([Date Of Review]@row) <= 3, 1, IF(AND(MONTH([Date Of Review]@row) > 3, MONTH([Date Of Review]@row) <= 6), 2, IF(AND(MONTH([Date Of Review]@row) > 6, MONTH([Date Of Review]@row) <= 9), 3))))
the result in the following screenshot
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Hi @Cheryl Collins
Hope you are fine, for the Second formula for Corrective Actions Timeline you need to create the following helper columns with those formulas:
1 Weekday =WEEKDAY([Date Of Review]@row)
2 Actual Days=IF(AND(Weekday@row = 6, [Corrective Action Timeline]@row = "Immediately"), 3, IF(AND(Weekday@row = 6, [Corrective Action Timeline]@row = "5 Working Days"), 6, IF(AND(Weekday@row = 6, [Corrective Action Timeline]@row = "10 Working Days"), 13, IF(AND([Corrective Action Timeline]@row = "Immediately", Weekday@row < 6), 0, IF(AND([Corrective Action Timeline]@row = "5 Working Days", Weekday@row < 6, Weekday@row <> 2), 6, IF(AND([Corrective Action Timeline]@row = "10 Working Days", Weekday@row < 6, Weekday@row <> 2), 13, IF(AND([Corrective Action Timeline]@row = "5 Working Days", Weekday@row = 2), 4, IF(AND([Corrective Action Timeline]@row = "10 Working Days", Weekday@row = 2), 11))))))))
3 Corrective Action Due Date =[Date Of Review]@row + [Actual Days]@row
the following screen shot show the results
you can test the formulas using the following Published Link
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Fantastic @Bassam.M Khalil Both have worked a treat! Thank you so much

Am glad it's worked and you are more than welcome.
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Help Article Resources
Categories
Check out the Formula Handbook template!