Forum Discussion

labtech3's avatar
labtech3
Copper Contributor
Jul 28, 2025

Conditional formatting based on the date output of a formula

I have 4 Columns that are linked, one has Analytes listed, there is not a set list of analytes or some kind of drop down because some are new and one time use. Two columns change the date shown based on the analytes listed in the first column and a base requested date, one is date + 5 which is a general due date, the other is a fast turn around time due date that changes the addition of days based on the original analyte listed. I need a fourth column to now change based on todays date or tomorrows date and whether the column has been filled in, denoting the analysis has been completed. There is a column for the day it was requested that these formulas use as the base date.

Date Analysis Requested (F) is entered manually.

 

Here is the general due date column (H):

=IF(H2="",F2+7,"")

Here is the more specific due date based on (K):

=IF(I2="TSS",F2+3,IF(I2="TDS",F2+3,IF(I2="NO2",F2+7,IF(I2="NO3",F2+7,IF(I2="F Vial",F2+2,IF(I2="Total Chlorine",F2,IF(I2="BOD",F2+3,IF(I2="Chloride Vial",F2+2,IF(I2="SO4 Vial",F2+2,IF(I2="pH",F2,IF(I2="PO4 Vial",F2+2,IF(I2="WI NH3 (ULR)",F2,IF(I2="E.coli",F2,IF(I2="pH dup",F2,IF(I2="Total N Vial",F2+2,IF(I2="Chlorine Vial",F2,IF(I2="NO2 Vial",F2+2,IF(I2="NO3 Vial",F2+2,IF(I2="% Solids", F2+3,IF(I2="Free Chlorine Vial", F2+1,IF(I2="Total Phosphorus Vial", F2+2,IF(I2="NH3 Vial",F2+2,IF(I2="CBOD",F2+2,IF(I2="R&D PFAS",F2+10,""))))))))))))))))))))))))

 

If H has a value, G stays empty to avoid confusion on the due date.

 

I need this final column to have color to indicate what is blank and needs completed by today or tomorrow, today being red tomorrow being orange and no rush staying green until the previous conditions I just stated apply.

Picture for how I'd like it to end up when finished.

excel
Formulas and Functions

1 Reply

  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    Jul 29, 2025

    So I would recommend creating a helper table for all those values and doing a lookup.

    alternatively you can use Name Manage and create a constant table value like this:

    but in either case if the table above or the name variable is 'tVals' then you just need to use:

    =IF($H2="",IFERROR($F2+VLOOKUP($I2,tVals,2,0),""),"")

    and if you need to compare with TODAY() or TODAY()+1

    you can create a couple conditional formatting formulas or just use the above formula - TODAY() to get a number 0 for today, ,1 for tomorrow, >1 for more days.  Then use 3 color scale formatting to do all 3 colors:

    notice the type is set to number and then values are 0,1,2

    or you can use ICON set and then use 'show icon only' so you can hide the numbers:

     

Resources