How to conditionally format cells within 14 days of a specific date

Hi all,

I am working on a personnel tracker. Each sheet in the workbook is a different week ending date. I have a cell at the top (B1) that contains the week ending date. I would like the projected end date column to highlight if anyone in that list has an outdate within 14 days of the week ending date. I don't want it to highlight if the date has passed, but if anyone's outdate is within 14 days of the week ending date, I would like it to highlight.

I am sure it isn't that difficult, but I am not figuring this one out. Hope this makes sense.


Thank you,
David

Hi
Can you please upload a sample of the file. It will make it easier to resolve your issue.
Cheers

Hi FoxSeaLady,

Thanks for the help! The forum won't let me attach the sample file for some reason, so here is a link to download it:

https://www.dropbox.com/s/uckc5gtcay...cker.xlsx?dl=1


For this example, the column I would want to apply the conditional formatting to would be 'D' (the anticipated wrap dates). At the top, in the red cell, I would enter the week ending date (in this case, 11/4/17). From there, any ANTICIPATED WRAP DATE that is within 14 calendar days (in this case, it would be Ed and Cindy) I would want to highlight in red. If the wrap date is further than 14 days out (in this case, Craig and Mary), it would remain unformatted and if the wrap date is in the past (in this case Bill), it would either remain unformatted, or I would have the cell turn a dark grey to show that the person is no longer working.

Hope this makes sense.

Thank you!!
-David

Hi FlameRetired

Yeah, I was thinking about that as I was posting the link. Thank you for the instructions on attaching. I have attached the file here.

Thank you again for all your help!!

-David

Try

CF rule:

=AND($D3>TODAY(),TODAY()+14>$D3)

Thank you, JohnTopley. The only problem, though, is that the CF rule you suggest uses TODAY's date to determine the 14 day window. I need it to use the date in B1 to determine the 14 day window.

Thanks,
David

Just change TODAY() in the formula to B1

=AND($D3>$b$1,$B$1+14>$D3)

Thank you, JohnTopley! That worked perfectly. I appreciate everyone's help.

-David

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.