IF function for notifying a deadline

Hello Angelique,

Thank you for your post. This is indeed possible. You need to use Conditional Formatting to achieve this because formulas in cells can't apply formatting.

Say the start date is entered into cell A2 and the completion date should go into cell B2. Select cell B2, then click Conditional Formatting in the Home ribbon. In the Conditional Formatting drop-down, click New Rule, then click "Use a formula to determine which cells to format". In the field below "Format values where this formula is true" enter the following formula: =AND($A2<>"",$A2<TODAY()-14,$B2="")

Adjust the cell references in this formula to apply to your data.

This formula checks for two things:
1. Whether the Start Date is older than two weeks
2. Whether the Completion Date cell is empty.

If no start date is entered, the completion date cell won't go red, only if the start date is older than 2 weeks.

Next, click the Format button further down. In the new window which opens, select the Fill tab and select a red colour. Click OK and OK again. This will apply the rule to cell B2. To copy this rule to more cells, make sure the cell containing the rule is empty, then drag the fill handle (bottom right corner of the cell) down as far as it needs to go.

Your sheet should be ready to use.

I hope this helps
Kind regards
Marius Barnard
Excel Trainer