Forum Discussion

Exwiz2485's avatar
Exwiz2485
Copper Contributor
Nov 17, 2022

Mark upcoming annual date in Exel Sheet

Hello All,

 

I am working on a mandatory training tracker for work. The input date is when the employee did the annual training last and I'd like it to be marked if the one year is about to expire. I tried to do conditional formatting but never was able to get all of the 14 trainings to mark.

For example:

Employee A did Training 1 on 09-Nov-22, it expires 09-Nov-22 and is supposed to show up marked 14 days prior to expiration. 

 

Thank you in advance.

 

 

excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Nov 17, 2022

    Exwiz2485 

    =D$1>=DATE(YEAR($B2)+1,MONTH($B2),DAY($B2))-14

    You can try this rule for conditional formatting as shown in the example.

    =$D$2:$S$10

    In the example the formatting is applied to this range.

    • Exwiz2485's avatar
      Exwiz2485
      Copper Contributor
      Nov 22, 2022

      OliverScheurich 

       

      It's still giving me grief - trying a different approach now.

       

      Date in Training X Column A will be valid for 1 year - what IF calculation would I have to put in there to have DUE show up in Column B 14 days prior? (In this case 17 January 

Resources