Forum Discussion
Time Conditional Formatting Help
That's like
In formula it shall be reference on first cell in the range if that's not M1.
In future please submit small sample file instead of / in addition to copy/pasted as text range.
I attached a sample of my data, I am using Office 2016.
When I tried it, it wouldn't work the way it did for you. Does it matter that I factored in headers? I made it M2 instead of M1. Not sure if my picture that I tried to upload worked, I can't see it again.
- SergeiBaklanSep 19, 2020Diamond Contributor
Another option compare to what HansVogelaar suggested is keep the range to which conditional formatting is applied as it is, i.e. entire column C. I'm not sure why you convert time difference into the text, but we also could keep it as it is.
With that we may apply the rule as
using absolute references on Start/End columns and relative reference on current row, starting from very first row of the range, i.e. first one.
With that it doesn't matter which information you return into column C, we only format it depends on values in Time and Actual Time columns.
Bottom line - creating formula for the rule always do it for the first cell of the range to which the rule is applied and take care about absolute/relative references to be sure formula correctly works if virtually move it on another cells.