Conditional formatting not applying correctly

Question

I am currently trying to use conditional formatting to highlight a section where the time is equal to or between two values.

When ran as a standalone formula, it works no problem. Unfortunately, when used as conditional formatting, it does not.

---

The formula I am using is:

=(Mod($A2,1)>=TimeValue(indirect("fall22SCHEDULE!"&cell("address", $H$2)))) * (Mod($A2,1)<=TimeValue(indirect("fall22SCHEDULE!"&cell("address", $I$2))))

---

While I originally though that my issue was with the sheet reference, I created another condition in a separate cell to check that, and that formula works.

I am not sure what I am doing wrong here. I have also tried wrapping the formula in an IF statement (if the result was >0, return TRUE).

---

Example Sheet: https://docs.google.com/spreadsheets/d/1yLT3L-DgiAeXE-H_m_wYHWvXjk7tyafroy2zBzYSNhM/edit?usp=sharing

• G2:G50 contains the formula working as intended (and demonstrating how I expect the conditional formatting to be iterating through the cells)
• H6 contains an example of the sheet reference working as intended within conditional formatting
• B2:B50 contains the conditional formatting rule
• Editing is on

---

References

• Formula: https://support.google.com/docs/thread/73798637/conditional-formatting-for-time-period-based-on-forms-timestamp?hl=en

• Sheet Reference: https://support.google.com/docs/thread/106075950/google-sheets-referencing-another-sheet-for-conditional-formatting?hl=en

Answer 1

you missed =

=(Mod($A2,1)>=TimeValue(indirect("fall22SCHEDULE!"&cell("address", $H$2)))) * (Mod($A2,1)<=TimeValue(indirect("fall22SCHEDULE!"&cell("address", $I$2))))