Quarterly audit formula

[jonwondon]

Hello everyone,

I work for an accounting department at a large resort chain.

We have to perform quarterly audits on the cash banks issued to select employees around the property.

Right now I have a master sheet that has every employee with a bank and the date that bank was issued in a left column - next to that (after their names) I have 4 columns (one for each quarter). Where I enter the date of the last audit.

We must perform an audit every quarter from the date they were assigned their bank.

Example:

Say, they received their bank on 1/20/22 - their next audit date would be 4/20/22. (~90 days)

For neatness I would like for it to be a conditional format just highlighting the names that are due for an audit. I’ve tried everything and cannot get it to work.

Anyone have any ideas on a formula?

 

[ExceLoki]

try this, it's looking at the max date in each row and adding 90 days to it, then comparing to today's date
---------------

Book1
	A	B	C	D	E	F
1	name	date issued	q1 audit done	q2 audit done	q3 audit done	q4 audit done
2	bob	12/24/2021	3/24/2022
3	ralph	2/1/2022	5/2/2022
4	john	11/27/2021	2/25/2022
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A4	Expression	=IF(MAX($B2:$F2)+90<=TODAY(),TRUE,FALSE)	text	NO

 

[Kerryx]

Book1
	A	B
1		Issue Date
2	Jim	22/03/2022
3	Joe	06/05/2022
4	John	01/10/2021
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A4	Expression	=$B2<=TODAY()-90	text	NO

 

[jonwondon]

I tried this and it doesn’t work on my end - not sure what the issue is.

try this, it's looking at the max date in each row and adding 90 days to it, then comparing to today's date
---------------

Book1
	A	B	C	D	E	F
1	name	date issued	q1 audit done	q2 audit done	q3 audit done	q4 audit done
2	bob	12/24/2021	3/24/2022
3	ralph	2/1/2022	5/2/2022
4	john	11/27/2021	2/25/2022
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A4	Expression	=IF(MAX($B2:$F2)+90<=TODAY(),TRUE,FALSE)	text	NO

 

[jonwondon]

This is only checking to see if it had been done in the last 90 days. Unfortunately this is not helpful to what I need. I need to know if an audit is due based on when the last audit was done per quarter.

Book1
	A	B
1		Issue Date
2	Jim	22/03/2022
3	Joe	06/05/2022
4	John	01/10/2021
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A4	Expression	=$B2<=TODAY()-90	text	NO

 

[ExceLoki]

try one of these:
--------------

Quarterly audit formula conditional format date.xlsx
	A	B	C	D	E	F
1	name	date issued	q1 audit done	q2 audit done	q3 audit done	q4 audit done
2	bob	12/24/2021	3/24/2022
3	ralph	2/1/2022	5/2/2022
4	john	11/27/2021	2/25/2022
5
6
7	name	date issued	q1 audit done	q2 audit done	q3 audit done	q4 audit done
8	bob	12/24/2021	3/24/2022
9	ralph	2/1/2022	5/2/2022
10	john	11/27/2021	2/25/2022
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A8:A10	Expression	=MAX(B8:F8)<=TODAY()-90	text	NO
A2:A4	Expression	=MAX(B2:F2)+90<=TODAY()	text	NO

 

[jonwondon]

try one of these:
--------------

Quarterly audit formula conditional format date.xlsx
	A	B	C	D	E	F
1	name	date issued	q1 audit done	q2 audit done	q3 audit done	q4 audit done
2	bob	12/24/2021	3/24/2022
3	ralph	2/1/2022	5/2/2022
4	john	11/27/2021	2/25/2022
5
6
7	name	date issued	q1 audit done	q2 audit done	q3 audit done	q4 audit done
8	bob	12/24/2021	3/24/2022
9	ralph	2/1/2022	5/2/2022
10	john	11/27/2021	2/25/2022
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A8:A10	Expression	=MAX(B8:F8)<=TODAY()-90	text	NO
A2:A4	Expression	=MAX(B2:F2)+90<=TODAY()	text	NO

No, neither work - I am typing this formula in the -> New Rule -> Use a formula to determine which cells to format.

I’ve double checked all the formats for dates and everything.

 

[ExceLoki]

and you're adjusting the formula for your range?
could you share you data so i can see what you're working with?

 

[jonwondon]

and you're adjusting the formula for your range?
could you share you data so i can see what you're working with?

That fixed it, it was running the conditional formatting for one range - thank you for your help

 

[ExceLoki]

no prob, glad to help