Need help with conditional formating with expiration dates

I have a training spreadhseet that has date training was completed. Some of the training may only needed to be taken once and some others may have a varied frequency rate. I am trying to format the spreadsheet to where the date the training was taken comes up "red" when it reaches the required frequency for the next training session. (i.e.,training that needs to be done 1 year from the training date)

I am also trying to set up the spreadsheet to where this same training dates will turn "yellow" when they are 180 days from the required frequency for the next training date.

I have tried all sorts of different methods to get the conditional formatting set up but to no avail.

Hi rhoover7420 and welcome to ExcelForum,

Try the following conditional formatting (tested and working in Excel 2003):

=DATEDIF(E25,TODAY(),"y") >0

=DATEDIF(E25,TODAY(),"d") > 180

Lewis

Hi LJMetzger,

Unfortunately, this did not work. I am running Excel 2013, not sure if there will be a difference from the earlier versions or not. The one key piece in this spreadhseet formatting is the fact that the frequency interval is the key to what triggers the next training date. Some may have a frequency of 0 which menas trianing is only done once and other may have a frequency of 2 which would mean the training takes place every two years from the last date. Hopefully this will shead a little more light on what i am trying to do with this type of formatting.

Ray

Hi Ray,

I couldn't find the 'Frequency Interval' anywhere in the file. It's probably hiding in plain sight somewhere. Please point me in the correct direction.

Lewis

Lewis,

I see where I mistakenly put Position in A2 and frequency in B2 of the spreadsheet, all of row 2 is for the frequency intervals. Sorry about the confusion. Most of the frequency intervals are at "0" right now but , look in "M2" and you will see the frequency is at "4".

Ray

Ray,

I was right about the Frequency hiding in plain sight. Try out the attached sample file, which contains an excerpt from the 'Actual' sheet. It allows changing of the dates and the frequencies (using VBA) to test the CONDITIONAL FORMATTING.

I added a lookup table (using Excel function VLOOKUP) to account for the various year frequencies. The lookup table uses a NAMED RANGE. There are instructions in the sample file if you are not familiar with NAMED RANGES. The Named range allows you to put the lookup table anywhere you want on any sheet in the workbook, without changing the CONDITIONAL FORMATTING formulas.

My proposed new conditional formatting is:

=DATEDIF(C4,TODAY(),"y") >=VLOOKUP(C$2,MyYearTable,2)

=DATEDIF(C4,TODAY(),"d") >= VLOOKUP(C$2,MyYearTable,3)

The VLOOKUP arguments are:
1. Frequency
2. Lookup Table Named Range
3. Column in the Lookup table

Column 2 is years (red formatting)
Column 3 is days (yellow formatting)

If I don't have what you want correct, I think I am pretty close.

Lewis

Lewis,

Looking at what you did in your example it looks like this will function properly. However, I am having a bit of a problem copying the lookup table over to my actual spreadsheet and setting up the conditional formatting. I am not sure what I am doing wrong. Can you add the look up to my original spreadsheet so I can see what you are adding so I can get my arms around this.

I appreciate your help.

Ray

Hi Ray,

No problem. While moving the data over, I ran into a small problem (when a cell was blank) and had to add additional CONDITIONAL FORMATTING for that condition. See the attached workbook, and the attached CONDITIONAL FORMATTING jpg.

I put the Lookup Table on Sheet2 for convenience (for me). You can cut and paste the lookup table to anywhere in the workbook, and it should work ok. You can even hide the lookup table in plain sight by applying custom formatting of 3 semi-colons [;;;] [no brackets] to each cell.

Lewis

Lewis,

I see know what I was doing incorrectly. I appreciate your help, thanks again for everything.

Ray

I'm glad it worked out Ray. Please feel free to ask if you are have any more questions and/or problems.

Lewis