Cell date and colour changes for expiring dates

Question

Alert me
|
Edit
|
Change type
Question
You cannot vote on your own post
0

Hello

I am using Excel 365 and I have a spreadsheet that contains staff names, dates they achieved trainings and dates they completed tasks.

When I input the date that the training/tasks were completed, I want to format the cell so that it automatically changes to the expiry date. 4 months, 6 months, 1 year, 2 years, 3 years. Also to change the cell green.
I also want to change the colour of the cell to orange when they are 30 days within expiry date. Change to red when it has passed the expiry date.

For example, Lisa completed training on 01/11/2020 that requires renewal in 2 years. When I input that date I want to automatically change to 01/11/2022 and change the cell green colour. When it is 01/10/2022, 30 days before the expiry date, I want the cell to change to orange. When it goes passed 01/10/2022, I want the cell to change to red.

I tried looking online but was not understanding.

If anyone could help, I would very much appreciate it.

Thanks.
Jamie

Answer 1

Hi @Jamie Stevens

Very rainy day so I spent a few hours on your request. In this .zip file you'll find a macro enabled workbook (.xlsm) that does what you want (assuming I understood everything):

NB:

• Data validation is in place to ensure Dates are enterred in the Table
• VBA code assumes the values it deal with are Dates
• According to your picture, column names must end with "mnths" or "yr" and the digit (nb of months/years) must be just before (no space)
• If you add columns to the right of the Table you'll have to adjust the Conditional Formatting rules 'Applies to' range

I tested a few scenarios and this looks good to me. I'm not saying this is bug free though...
If this solves your problem please mark this reply as answer to help others with a similar issue. Thanks in advance + Nice day...

Answer 2

Hi @Jamie Stevens

With TODAY = Nov 6th 2020, does the following reflect what you expect? If not please upload a similar picture with dates and colors you expect in [Expiration date] column. Thanks

Answer 3

@Jamie Stevens

When I input the date training or tasks were completed, I want it to automatically change to the date in 3 years, 1 year etc

What you're asking is only doable with VBA, something I avoid as much as possible if you see what I mean. Sorry, I pass

Good luck...

Answer 4

Hi @Jamie Stevens

In the 1st picture you only had 1 column (Staff name) before the Mnths/Yr columns. You now have 10 :(

I made the appropriate changes in this zip file. If you're going to add/remove columns before the Mnths/Yr you'll have to update the VBA code accordingly:

• Alt+F11
• Select Module = Change
• Go to Sub WorksheetChange (it's at the top of the module)
• Replace value 10 I squared in red below:

Answer 5

Hi again @Jamie Stevens

I'm not a VBA star at all :( Just discovered a serious problem in case you would select several cells at the same time in the Mnths/Yr columns and Ctrl+Enter a non-Date value. The Data Validation I put in place earlier would reject the value but VBA would +/- crash later

Had to change the approach:

• No more Data Validation. Done via VBA now
• So you don't have to scroll down in the code to change the 10 discussed earlier you'll find variable FirstColToIgnore at the top of Sub WorksheetChange in Module Change:

Updated version avail. here as a zip file. Sincere apologies & Nice day...