98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » Flagging up dates due for renewal
Flagging up dates due for renewal
Resolved · Low Priority · Version 2003
Kathleen has attended:
PowerPoint Intermediate Advanced course
Flagging up dates due for renewal
I have a training matrix which I need to flag up dates of courses before they expire so I can book renewal courses etc. How can I do this??
RE: flagging up dates due for renewal
Hello Kathleen
Thank you for your question.
Is there a certain length of time before the course is due to expire that you want to flag up the expiry date; and how do you want to do that (e.g. make the dates come up in red)?
Kind regards
Amanda
RE: flagging up dates due for renewal
HI AMANDA
YES WE NEED TO FLAG DATES DUE TOEXPIRE 2 MONTHS AHEAD OF TIME
I HAVE THE MATRIX IN EXCEL BUT CAN COPY OVER TO ACCESS IF THIS IS EASIER
FLAG UP IN RED IS IDEAL, ALSO IS THERE A WAY OF LINKING THE FLAGGING UP IN OUTLOOK?
WE HAVE AN AUDIT HERE ON MONDAY AND I WOULD REALLY LIKE TO SOLVE THIS BY THE IF POSSIBLE
THANKS
KATHLEEN
RE: flagging up dates due for renewal
Hello Kathleen
Apologies, I've been off sick the last two days so I've only just picked up your latest post today.
Not sure if you've found a solution ahead of your audit in the interim, but I've attached an example of a solution which I think could work regarding highlighting upcoming expiry dates.
What I've done is put the TODAY function in cell A1. This will update daily, to show the current date.
On the cells coloured in green (A5:A15), I've applied conditional formatting that turns the date red if it falls (roughly) within 2 months ahead of the date in cell A1 (remembering that this date will keep updating itself).
If you select cells A5:A15, and go to Format - Conditional Formatting, you will see the rule I've set up to apply the format.
What the rule does is take the expiry date and subtracts the current date in cell A1, and if the difference is less than or equal to 60 (days, which is roughly 2 months), then the dates in these cells will turn red (set by clicking the Format... button within Conditional Formatting).
You can experiment with this by changing one of the dates currently showing in black to a date within (roughly) two months of the current date and it should change to red.
With regards to linking this to Outlook, this is something that is not straightforward, and I imagine would involve VBA coding to sort out.
Let me know what you think.
Amanda
Attached files...
Training information:
See also:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Create Charts with One keystrokeCreate a graph with one click |