I am trying to add conditional formatting that turns yellow after 48 hours from the date in the cell, turns orange after 72 hours from the date in the cell, and red after 96 hours after the date in the cell.

  • 1
    Great! What have you already tried that didn't work? – ale Aug 8 '16 at 13:53

All of the hours you are express can be simplified as days. (2 days, 3 days, 4 days ). You want a condition per color you are using.

Assuming you are doing this on a desktop: If you select the range you want to apply the formatting. Take note of the top-left-most cell of the range & write it down. Right click on the range you selected and select "conditional formatting." Then select "Add new rule." In the drop down menu labled "Format cells if..." select "Custom formula is." Then in The input-box below type out your yellow formula. ( Repeat all of the steps for Orange, & Red ).

Below I listed the Yellow, orange, & red formulas I used assuming your dates are in column A Ranges A2:A1000

  1. YELLOW: =AND(TODAY()>=2+A2,TODAY()<3+A2)
  2. ORANGE: =AND(TODAY()>=3+A2,TODAY()<4+A2)
  3. RED: =(TODAY()>=4+A2)

Remember when I asked you to take note of the top-left-most cell? If you want to adapt the formula I used in the list above on the Range you are applying your conditional-formatting to, You need to change "A2" into the top-left-most-cell you wrote down.

| improve this answer | |
  • This is for Google Sheets. Why does the desktop OS matter? – ale Aug 8 '16 at 16:38
  • Because the exact steps I provided are not identical to the Android-App, or the iOS-App, or even the mobile-site-version of google sheets(If you try doing it on your cell phone via your cell-phones web-browser). – Xzila Aug 8 '16 at 16:48
  • As this is Web Apps, and not Android Enthusiasts or Ask Different, it's assumed that your solution would be for a desktop browser. Your solution should work just as well for Mac or Linux, on virtually any browser. – ale Aug 8 '16 at 17:00
  • Please suggest an edit that will help people avoid trying to apply this solution on their phone. – Xzila Aug 8 '16 at 17:13

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