2 Formulas Required

Cheryl Collins

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

Bassam Khalil

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

Bassam Khalil

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

Bassam Khalil

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

Bassam Khalil

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

Cheryl Collins

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

Bassam Khalil

@Cheryl Collins

Am glad it's worked and you are more than welcome.