Forum Discussion

Jna3276's avatar
Jna3276
Copper Contributor
Oct 01, 2024
Solved

Past Date Conditional Format

Hi

Id like to conditional format column A to red fill if the date is 5 working days or more.

 

If blank then ignore or leave as is.

 

If it can't be done for working days, then 7 days and over is fine

 

I could have today's date  in cell D2, in another sheet called 'BH'

 

Thanks

Excel
Formulas and Functions
  • BobOrrell's avatar
    BobOrrell
    Oct 01, 2024

    Jna3276 
    This should work for you =AND(C4<=WORKDAY(TODAY(),-5),ISNUMBER(C4))

    change the 2 instances of C4 to the first cell of the range you are formatting

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Oct 01, 2024

    Jna3276 

    My formula is designed to meet the way I work (no direct cell references etc.)

    = IF(@date, NETWORKDAYS(@date, today) > 5)


today
=BH!$D$2
or 
=TODAY()

    Others work differently.

  • BobOrrell's avatar
    BobOrrell
    Iron Contributor
    Oct 01, 2024

    Jna3276 
    This should work for you =AND(C4<=WORKDAY(TODAY(),-5),ISNUMBER(C4))

    change the 2 instances of C4 to the first cell of the range you are formatting

Resources