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
    Commented Aug 8, 2016 at 13:53

1 Answer 1


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.

  • This is for Google Sheets. Why does the desktop OS matter?
    – ale
    Commented Aug 8, 2016 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
    Commented Aug 8, 2016 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
    Commented Aug 8, 2016 at 17:00
  • Please suggest an edit that will help people avoid trying to apply this solution on their phone.
    – Xzila
    Commented Aug 8, 2016 at 17:13

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