#1
|
|||
|
|||
Identify when a date is to expire
Hi all, I am new to this forum and have a question that may have been asked before but I cannot find it or a solution. I have been asked by a friend how to set up excel so that when their staff complete a test on a certain day that they can be identify when 5, 11 and 23 months pass as a reminder that the test is expiring and needs to be retaken. I have looked on the internet but all scenarios seem very complex and not really what is required for a basic elapsed time test. Many thanks in advance. |
#2
|
||||
|
||||
You can use conditional formatting to colour the cells accordingly.
Assuming your dates are in column A beginning at A2, then select the range from A2 to bottom of range and go to Home|Conditional Formatting|New Rule. Select "use a formula to determine which cells to format" then enter formula: =DATEDIF($A2,TODAY(),"m")>=23 Click Format and choose from the Fill tab. Click Ok, then New Rule and repeat with formula =DATEDIF($A2,TODAY(),"m")>=11 and again with formula =DATEDIF($A2,TODAY(),"m")>=5 Make sure the formula are displayed in the above order (you can move each up or down with the arrow tools), then check the "Stop if True" checkboxes in the last column. Last edited by NBVC; 06-30-2017 at 11:46 AM. Reason: typo |
#3
|
|||
|
|||
Thank you for your reply NBVC I am nearly there but I don't think I explained it well. basically if column A2 has a set of dates from Jan to Jun this year where test have been taken, I would like column B2 to let me know after 5 months that it was up for renewal and then C2 to notify me after 11 Months and finally D2 after 23 Months. This is because the test expire at different times for different users. I hope this makes it clearer!
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Identify Purchasers who bought products on the same date | ophelias | Excel | 1 | 12-20-2016 07:13 PM |
Please help me Identify this font!!! PLEASE!!! | krammkracker | Word | 1 | 10-13-2015 12:06 PM |
VBA to identify how Word was invoked | Sorcerer13 | Word VBA | 7 | 04-24-2014 11:15 AM |
How do you identify the ID of a picture in PowerPoint? | CatMan | PowerPoint | 4 | 04-17-2012 03:29 PM |
identify non-members from two lists | rterrain03 | Office | 5 | 12-01-2011 01:41 PM |