flagging up dates due

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Flagging up dates due for renewal

Flagging up dates due for renewal

resolvedResolved · 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...

expiry dates conditional formatting.xls

RE: flagging up dates due for renewal

THANK YOU SO MUCH FOR THE SOLUTION TO MY PROBLEM

 

Training courses

 

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
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Create Charts with One keystroke

Create a graph with one click

1. Select your data.
2. Press F11.
3. You have a graph.

View all Excel hints and tips


Server loaded in 0.14 secs.