In Google Sheets I would like to color a cell depending on if the cycle is on or off.
The cycle is for 10 day the cycle is ON then for 5 days the cycle is OFF then repeat.
So if I start the cycle on Jan 1. The cycle would be ON from Jan 1 – Jan 10 then OFF from Jan 11- Jan 16 then ON from Jan 17 – Jan 27 then OFF from Jan 28 – Feb 1 then continue with this pattern for the year or forever.
Best Answer
As Tedinoz said in the comment
Start by identifying the relevant date, then calculate the day of the year for that date - use the days function. For example, March 15, 2020 is the 75th day of the leap year. Now calculate the day in the 15-day cycle - calculated manually, this is day 15; use the mod function to express this mathematically - mod(75,15) equals 0. Now apply conditional formatting with 3 rules: #1 if mod if between 1 and 10 (the ON Cycle); #2 if mod is between 11 and 14 (the Off cycle); and #3 if mod =0 (also the OFF cycle)