cell change base on date

Hello,


I have a sheet that I'm having trouble getting the right color to apply to the cell. I have 3 colors: green (1-7 days), yellow (8-14days), and red (15+ days).

The trigger for 8-14 seems not to be working. See attachment for details.


Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Yes, exactly - you'd put the formula in a helper column and then set it to a Column Formula which would apply it to every row. Then you can base the formatting off this column.

    You could stay with the Automation instead, but then you'll want to adjust your conditions where the date is in the last 14 days but also not in the last 7 days. Does that make sense?

Answers

  • Have you considered using conditional formatting? It may be easy to do there?

  • Melissa  Rojas
    Melissa Rojas ✭✭
    edited 04/25/22

    @Corey Freedman I'm not familiar how to do that. Can you recommend how to set it up for the 3 colors? I've used conditional formatting for cells when they are in high priority vs low and it worked. I just don't know how to create it based on a date. I need to be able to track how many findings were each color at the end of the year.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi Melissa,

    I presume you're looking to replace the "1-7 Days" with "8 - 14 Days", is that correct?

    If so, your Condition Block is what's preventing this from happening. You're currently excluding any rows that have "1-7 Days" selected.

    I would potentially suggest using a formula in a helper column to identify the number of days from "date issued" to Today's date. Then you can use this in your Condition block or Conditional Formatting, whichever you prefer.

    For example:

    =TODAY() - [Date Issued]@row

    See: TODAY Function and Use Formulas to Perform Calculations With Dates

    Cheers,

    Genevieve

  • @Genevieve P. I also have a "closed date". Basically, I have to be able to track how many items were at each station at the end of the year. My concern with formulas is that the date will continue to run and all end up at 15+ days.

    I have a trigger that when the "completed date" is added to move the item to a different sheet, and I don't know if that will solve it or not.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi Melissa,

    You can write a formula that only uses "TODAY" if there is no Closed Date, otherwise use the Closed Date:

    =IF([Closed Date]@row = "", TODAY() - [Date Issued]@row, [Closed Date]@row - [Date Issued]@row)


    This way if the row is closed it has a static number, but if it's still open, the day count goes up every day. Would that work for you?

  • @Genevieve P. I think that might work. Would I then add a "helper row" to add the formula and base the conditional formating based on that row? Currently, I have a drop-down column that has 1-7 days with conditional formatting to turn it green, 8-14 days (turns yellow), and 15+ (turns red).

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Yes, exactly - you'd put the formula in a helper column and then set it to a Column Formula which would apply it to every row. Then you can base the formatting off this column.

    You could stay with the Automation instead, but then you'll want to adjust your conditions where the date is in the last 14 days but also not in the last 7 days. Does that make sense?

  • @Genevieve P. So if I leave the conditioning, I would set it up as follows:


    1-7 days - the date is in the last 7 days

    8-14 days - the date is in the last 14 days but also NOT in the last 7 days.

    15+ - the date is NOT in the last 14 days


    Is that correct?

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi Melissa,

    Yes! But for the 15+ you would also want to make sure you add criteria that says the date is in the Past:


    Then in your formatting rules you'll probably want something that also checks to see if that row is Completed or not (perhaps using that Closed Date column?) so that it's not Red if it's simply in the past.

  • Okay, that makes sense.

    I have updated the 15+ to include in the past (I had "Issued Date is NOT blank"). I need the status to remain as-is, since it feeds to the YTD dashboard. So I have the following trigger set up to move to a different sheet if a date is added to the "Date Closed" column. Do you see any issues here?


  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Melissa Rojas

    Looks good to me! How did it run this morning?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!