I have created the following formula to determine my time shift:
=SI(O(MES(HOY()) = 7; MES(HOY()) = 8); "7:00"; SI(DIASEM(HOY(); 2) = 5; "6:00"; "8:45"))
I want to establish a comparison with another cell that returns the hours worked for any given day, so that if the minimum established by the formula is not reached, the cell is colored red.
The cell in question, D12
, adds the time spent on the different tasks, =SUMA(D2:D11)
:
All cells that do not include text have the following format:
The "Start" and "End" columns represent the start and end time of each task, and "Duration" the duration in hours of the task, such that =B2-A2
, and so on for the rest of the tasks.
The problem, I think, is that Excel doesn't recognize the format of the value of F12
and the one returned by the conditional formula as equivalent, so the color, applied using the "Conditional Formatting" option, doesn't work correctly.
How could I fix it?
Excel does not recognize the equivalence between cell D2 with the result of the formula that determines your daily shift because the first is read as a number and the second as text. In this case, you can use the TIMEVALUE() function to convert the text to a decimal number.
Knowing this, you can now apply the color:
Conditional Formatting > New Rule > "Use a formula to determine which cells to format." There you type the formula you created, but taking into account the conversion from text to number that you must perform:
You choose the format with the color of your preference, you click on "Accept" and cell D2 should already take the color you chose for values less than that indicated by the formula of your daily shift.