Hi there! Need an excel time formula.
I need to make all the cells in column C turn red if a time entered in the connected column B cell has passed four hours.
Then I need the cell in column C to turn back to white once a time has been entered.
For example:
If I enter 12 pm in B2 and four hours goes by, I need C2 to turn red. Once I enter a time in C2, I need the cell to turn back to white.
Any help would be appreciated. I only know how to do this with days.
Time formulas
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Time formulas
Welcome to Eileen's Lounge!
Select the range in column C that you want to format, from C2 down. C2 should be the active cell in the selection.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=AND(B2<>"", B2<MOD(NOW()-TIME(4, 0, 0), 1), C2="")
Click Format...
Activate the Fill tab.
Select red as highlight color.
Click OK, then click OK again.
Select the range in column C that you want to format, from C2 down. C2 should be the active cell in the selection.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=AND(B2<>"", B2<MOD(NOW()-TIME(4, 0, 0), 1), C2="")
Click Format...
Activate the Fill tab.
Select red as highlight color.
Click OK, then click OK again.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 2
- Joined: 06 Feb 2024, 00:47
Re: Time formulas
That worked! Thank you!