cell change base on date

Melissa Rojas

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.

Genevieve P.

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?

Corey Freedman

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

Melissa Rojas

@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.

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

Melissa Rojas

@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.

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?

Melissa Rojas

@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.

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?

Melissa Rojas

@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.

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.

Melissa Rojas

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.

Hi @Melissa Rojas

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