autocalculate expiry date / expiry date colour warnings

**kimbling1** · 09-21-2010, 02:21 PM

I have a 2 part problem and admit to being an excel novice so I need simple step answers please.

The spreadsheet I have created has a column where the date individual staff members complete different training serials is entered. (Each row has a different staff member and different type of training listed).

Firstly, what I need excel to do for me is: in another column add an automatic expiry date for that training: however different types of training have different expiry dates ranging between 1 - 4 years. Is this possible to do and if so how?

Secondly, I need to change the colour of the expiry date cell to: green when it is within 3 months of the expiry date, yellow when it is within 1 month of the expiry and red once the training is expired.

Sorry, I know it's a lot to ask, but is it possible?
Thanks
KC

**daddylonglegs** · 09-21-2010, 02:36 PM

For the first part it would be best if you ceated a table with training types in one column and then the number of months (?) in the next, e.g. training types in Y2:Y10, number of months for each in Z2:Z10

Then if you have a date in A2 and the training type in B2 you can use this formula in C2 for the expiry date

=EDATE(A2,VLOOKUP(B2,Y$2:Z$10,2,0))

Note that EDATE is an Analysis ToolPak function in Excel 2002. If the above doesn't work check that you have ToolPak installed

Tools > add-ins > tick "Analysis ToolPak"

**kimbling1** · 09-21-2010, 03:34 PM

Thanks for such a quick reply Daddylonglegs.

Unfortunately the way the data is entered is with the staff members name in column A, so that all their individual training is shown in a block running down the rows in column B. The next staff member then follows on (using the same format). We have found this the easiest way to use the filters to be able to display and print the variety of reports we need in the correct format.

As training scenarios are repeated for individual staff, is there a formula to do the following?:

if column B = "update training" , populate column D with an expiry date of 1 year from the date entered in column C

and

if column B = "statutory training" , popupulate column D with an expiry date of 4 years from the date entered in column C

--
I have actually managed to solve the warning colours part of the problem using conditional formatting (,trial and error) and using the formulas:

=D2-TODAY()<=0 (with format to turn red)

then adding
=D2-TODAY()<=30 (with format to turn yellow)

and finally adding
=D2-TODAY()<=90 ( with format to turn green)

Maybe this will be of help to someone else with a similar issue to solve

**daddylonglegs** · 09-21-2010, 03:47 PM

OK, try this formula for D2

=IF(B2="","",DATE(YEAR(C2)+IF(B2="Statutory training",4,IF(B2="update training",1,"")),MONTH(C2),DAY(C2)))

or using EDATE

=IF(B2="","",EDATE(C2,12*IF(B2="Statutory training",4,IF(B2="update training",1,""))))

**kimbling1** · 09-21-2010, 05:13 PM

Fab many, many thanks!!!!!

having managed to add another training title successfully into my formula below..now to push my luck a little further..... if I wanted to add numerous other differently titled "specialist training" , all with an expiry date of a year, how do I fit that into this formula?

Do I have to list them all as below individually, or is there a way to group all the " "'s with a year expiry into one bit?

=IF(C2="","",DATE(YEAR(D2)+IF(C2="EMSB",3,IF(C2="Stat A",1,IF(C2="Stat B",1,""""))),MONTH(D2),DAY(D2)))

thanks again for all your help!!!!

(sorry forgot to add everything has been moved along 1 column, so this formula is applied to column E)

**kimbling1** · 09-21-2010, 05:50 PM

Another one to ask:

If I have a "one off training" which instead of an expiry date, I would like it to populate the cell with "N/A" how can I incorporate that?......

(promise no more questions after this)

**BAR SHAIK** · 08-27-2014, 01:35 AM

have a 2 part problem and admit to being an excel ID EXPIRATION so I need simple step answers please.

The spreadsheet I have created has a column where the date individual staff members complete different PASSPORT EXPIRY DATES is entered. (Each row has a different staff member and RESIDENTIAL PERMIT IS VALID FOR ONE YEAR HOW TO KNOW WHICH GUY PASSPORT & RESIDENTAL PERMIT IS EXPIRED .

Firstly, what I need excel to do for me is: in another column add an automatic expiry date for PASSPORT & RESIDENTAL PERMIT however different have different expiry dates ranging between 1 - 4 years. Is this possible to do and if so how?

Secondly, I need to change the colour of the expiry date cell to: green when it is within 3 months of the expiry date, yellow when it is within 1 month of the expiry and red once the training is expired.

Sorry, I know it's a lot to ask, but is it possible?

**BAR SHAIK** · 08-27-2014, 01:42 AM

have a 2 part problem and admit to being an excel ID EXPIRATION so I need simple step answers please.

The spreadsheet I have created has a column where the date individual staff members complete different PASSPORT EXPIRY DATES is entered. (Each row has a different staff member and RESIDENTIAL PERMIT IS VALID FOR ONE YEAR HOW TO KNOW WHICH GUY PASSPORT & RESIDENTAL PERMIT IS EXPIRED .

Firstly, what I need excel to do for me is: in another column add an automatic expiry date for PASSPORT & RESIDENTAL PERMIT however different have different expiry dates ranging between 1 - 4 years. Is this possible to do and if so how?

Secondly, I need to change the colour of the expiry date cell to: green when it is within 3 months of the expiry date, yellow when it is within 1 month of the expiry and red once the training is expired.

Sorry, I know it's a lot to ask, but is it possible?

**Fotis1991** · 08-27-2014, 02:53 AM

Welcome to the forum.

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.