CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Ajiri
Ajiri

Reputation: 17

Conditional Formatting Dates whereby specific date range auto-suggests action

I am in the process of creating a documentation tracker however struggling to do the following:

  1. add conditional formatting so that the background changes colour (green, yellow, red) based on how many days since documentation has last been reviewed/updated. So that the background colours change depending on the number of days or months whichever is easier.
  2. Based on the date, in the Action column for any dates greater than 3 months/90 days want it to automatically indicate check likewise for dates within a month or less no need to check

Link to documentation tracker google sheet: https://docs.google.com/spreadsheets/d/1DTyEg7JQcrTqCFQqgxrJEadOG8PcnI5khOrpOssZvyo/edit?usp=sharing

Example of a document which i got the status idea: https://docs.google.com/spreadsheets/d/1s8CXs-Q4qs7KeykxAlBF2cGfw7th-DGIqle6yLAVOxE/edit?usp=sharing Thanks in advance! Will be extremely grateful for any help, guidance and advice!

Upvotes: 0

Views: 1081

Answers (2)

kirkg13
kirkg13

Reputation: 3010

Okay, it is not a very elegant solution, and I know some experts would have a much better solution, but I think it does what you want.
See my tab "Iteration 2-GK" in your new sheet.

You can change the dates in column I to see if the Summaries colours change the way you expect. I haven't done Technical or Stakeholders yet, but they would be the same.

I came up with a complex formula to apply the same logic used in the conditional formatting rules for Column I, to apply conditional formatting for column C. Note that I added a hidden column B, to add the ProductName onto each row. I also removed the merged cells in columns A-E. The formula to check whether the dates in column I fall in a certain range, and then to count which range occurs most frequently (ie. also the most frequent colour in column I for each product) is:

=iferror(choose( 
   match(
   max( 
    {iferror(query({B$3:B,I$3:I},
            "select count(Col2) where Col1 = '"& B3 & "' and Col2 < date '"&TEXT(today()-180,"yyyy-mm-dd")&"' label count(Col2) '' ",0),""), 
     iferror(query({B$3:B,I$3:I},
            "select count(Col2) where Col1 = '"& B3 & "' and Col2 < date '"&TEXT(today()-31,"yyyy-mm-dd")&"' and Col2 >= date '"&TEXT(today()-180,"yyyy-mm-dd")&"' label count(Col2) ''",0), ""), 
     iferror(query({B$3:B,I$3:I},
            "select count(Col2) where Col1 = '"& B3 & "' and Col2 < date '"&TEXT(today() ,"yyyy-mm-dd")&"' and Col2 >= date '"&TEXT(today()-31, "yyyy-mm-dd")&"' label count(Col2) ''",0), "")}), 
    {iferror(query({B$3:B,I$3:I},
            "select count(Col2) where Col1 = '"& B3 & "' and Col2 < date '"&TEXT(today()-180,"yyyy-mm-dd")&"' label count(Col2) '' ",0),""), 
     iferror(query({B$3:B,I$3:I},
            "select count(Col2) where Col1 = '"& B3 & "' and Col2 < date '"&TEXT(today()-31,"yyyy-mm-dd")&"' and Col2 >= date '"&TEXT(today()-180,"yyyy-mm-dd")&"' label count(Col2) ''",0), ""), 
     iferror(query({B$3:B,I$3:I},
            "select count(Col2) where Col1 = '"& B3 & "' and Col2 < date '"&TEXT(today() ,"yyyy-mm-dd")&"' and Col2 >= date '"&TEXT(today()-31, "yyyy-mm-dd")&"' label count(Col2) ''",0), "")}, 
  0),"red","yellow","green"),"")

I haven't got this to work, so it needs to be dragged down column C from cell C3.

Will you need to add rows for a product, if you come up with more documentation? I haven't tested yet if this will work when adding rows.

Upvotes: 0

kirkg13
kirkg13

Reputation: 3010

Based on what you've provided so far, here is the basic conditional formatting to get you started. https://docs.google.com/spreadsheets/d/1_AvM9sPlxUSlToqWf7t6oSKak5K7ALc_S4XjgO5o4q0/edit?usp=sharing

The custom formula for the conditional formatting looks like this:

=AND(TODAY()-E2 > 90, E2 <> "")

So if the date in E2 is older than 90 days, mark that cell in RED. This rule applies to the whole column, E2:E.

Similarly, a checkmark is added in the Action column for dates older than 90 days, by the formula in F1. Do you also mean that dates less than 30 days old should be always unchecked, but dates between 90 and 30 days, should be left as they are, either checked or not?

Let us know if this is what you are hoping for.

Update: The Action column is calculated based on the difference between today's date and your date value in Column E. It is really duplicating the RED highlighting, so doesn't really help you. If it is a field that you want to be able to update, you'll probably need to change the Arrayformula to a dragdown formula, which you cana then overtype if putting a checkmark in a cell.

Upvotes: 2

Related Questions