0

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.

  • Welcome. Please add a brief description of your search/research effort as is suggested in How to Ask – Rubén Dec 31 '19 at 16:52
  • Welcome. This is not difficult if done in stages, and research @Rubén has focus. 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). – Tedinoz Jan 1 at 23:24
  • Thanks Tedinoz, spot on. – Pat Jan 2 at 1:20
0

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)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.