Forum Discussion
Exwiz2485
Nov 17, 2022Copper Contributor
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....
OliverScheurich
Nov 17, 2022Gold Contributor
=D$1>=DATE(YEAR($B2)+1,MONTH($B2),DAY($B2))-14You can try this rule for conditional formatting as shown in the example.
=$D$2:$S$10In the example the formatting is applied to this range.
Exwiz2485
Nov 22, 2022Copper Contributor
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
- OliverScheurichNov 22, 2022Gold Contributor
=IF(TODAY()>=DATE(YEAR(A2)+1,MONTH(A2),DAY(A2)-14),"DUE","")You can use this formula in cell B2.