Conditional formatting for elapsed time

[Eqwizzle]

I am wanting a formula that will color code the cells depending on the value which is elapsed time. I would like for the cell to turn red if past 24hrs, orange if past 10hrs, yellow if past 5hrs, and blue if past 1hr. I tried the formula =H2 > Time(24,0,0), H2 <> “”. But that formula isn’t functioning how I would like.

New to excel and I am a novice just learning from various online tutorials.

 

[bebo021999]

Only see the image, it is hard to tell what exactly under value of a time. 1:00:00 could be "18/10/2024 01:00:00" or next day "19/10/2024 01:00:00"
Try to check it by format cell as general to see what is real value.
In attach image you can see it works for me
Next, try to attach a file via link of gg drive, to see the actual spreadsheet.

 

[Eqwizzle]

Only see the image, it is hard to tell what exactly under value of a time. 1:00:00 could be "18/10/2024 01:00:00" or next day "19/10/2024 01:00:00"
Try to check it by format cell as general to see what is real value.
In attach image you can see it works for me
Next, try to attach a file via link of gg drive, to see the actual spreadsheet.View attachment 118229

Column H is where the elapsed time appears by subtracting F2-C2 and column H is set to [hh]:mm:as to accommodate the duration going pass 24 hours. In one of the images you can see the box is highlight red indicating that the duration of time is greater than or equal to 1 hour but it should be highlighted blue. Column C and column F is setup for time using the now function with the time being formatted as HH:MM:SS AM/PM. I hope that helps. It's for work so I couldn't attach the actual spreadsheet because it contains sensitive information.

 

[bebo021999]

You are using the TIME function to create a timestamp exceeding 24 hours, which is incorrect

In Excel, the TIME(hour, minute, second) function returns a time value stored as a decimal number, where 1 day equals 1. Therefore, if the number of hours exceeds 24, it will wrap around the 24-hour cycle. This means:

• TIME(25,0,0) is calculated as 25 hours, which is essentially 1 hour into the next day, so its result is the same as TIME(1,0,0), which is 1:00 AM.
• TIME(24,0,0) is calculated as 24 hours = 0

In other words, when the number of hours exceeds 24, Excel automatically wraps around to the beginning of the day, so 25:00:00 is the same as 1:00:00

Try using a fraction of 24 to represent the time, like I did in previous post.

 

[Eqwizzle]

After tweaking with it, what seemed to work was =AND($H2>=TIME(5,0,0),$H2<(10,0,0),$H2<>””). It was able to color code the cell yellow due to the value of the cell being equal to five hours but less than ten. I just formatted the formula to accommodate for the rest of rules.Tested it out and worked as expected.