Sep 15 2022 04:12 AM
Hello. I'm trying to write a formula based on dates in tables on different sheets. What i want is a cell on the first sheet to fill red and show "Passed due" if any of sheet2 E4:E8 = (Today() - E$) > 7. But if all of E4:E8 are blank or not > 7 to show nothing and no fill.
I have 6 total sheets for maintenance tasks, each is due in days of 7, 14, 30, 90, 180 and 365. These would correlate to the 1st sheet with this formula to each of 6 cells so when you open the document it would flag a particular sheet to let you know to check it.
Sep 15 2022 05:15 AM
Sep 15 2022 06:33 AM
Let me know if that link works or i can try a something else
Sep 15 2022 06:47 AM
SolutionIn B7:
=IF(COUNTIF(INDIRECT(SUBSTITUTE(B6,"-","")&"[Date Complete]"),"<"&TODAY()-7),"Past Due","")
Fill to the right to G7.
Select B7:G7.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Format on;y cells that contain'.
Leave the first drop down set to 'Cell Value'.
Select 'equal to' from the second drop down.
Enter Past Due in the box next to it.
Click Format...
Activate the Fill tab.
Select red.
Click OK, then click OK again.
See the attached version.
Sep 15 2022 06:49 AM
@Jgolden1980 Lets check the attached file and let us know your feedback.
Sep 15 2022 07:32 AM
Sep 15 2022 06:47 AM
SolutionIn B7:
=IF(COUNTIF(INDIRECT(SUBSTITUTE(B6,"-","")&"[Date Complete]"),"<"&TODAY()-7),"Past Due","")
Fill to the right to G7.
Select B7:G7.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Format on;y cells that contain'.
Leave the first drop down set to 'Cell Value'.
Select 'equal to' from the second drop down.
Enter Past Due in the box next to it.
Click Format...
Activate the Fill tab.
Select red.
Click OK, then click OK again.
See the attached version.