Angelique has attended:
Excel Advanced course
Excel Advanced course
IF function for notifying a deadline
Hi,
I have a spreadsheet that is shared amongst a team of people who all need to perform tasks related to the onboarding process for new employees. I want to set a deadline for people to complete a specific task for a new employee.
So, for example, if I've entered the start date of a new joiner and Iād like to give team members 14 days to complete their tasks for this new joiner, the deadline cell should be formatted to recognize and highlight once they pass the deadline and the cell remains empty (no completion date has been entered). I need something along the lines of If Today is later than the start date + 14 days, the cell should contain some conditional formatting that colours the cell red, so people will be prompted that they are late.
Is it possible to write a function in Excel that can do this for me?
Thanks
RE: 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
