Expiration based coloring, based on when training was done

**colinmarcum** · 08-28-2013, 11:13 AM

I am trying to create a spreadsheet of mandatory annual training certifications that change colors based on when the training was completed. Been experimenting with different formulas, but I just can't seem to get it to work.

Red= >365 days (past due)
Amber=365 to 335 days (30 days out)
Green= <335 days (more than 30 days out)

Basically I recieved the certificates and update the individual's cells with the completion dates.

Example:
John Doe
Cert A (31 OCT 12)
Cert B (30 NOV 12)
Cert C (31 DEC 12)

If the current date was -01 NOV 2013- then Cert A would be RED because it is 1 day past due, Cert B would be Yellow because he still has 29 days left, and Cert C would be Green because he has more than a month left.

Thanks for any help you are able to render.

-Colin Marcum

**liz5818** · 08-28-2013, 11:28 AM

i had a similar project. here's what i did.

First i set up when the annual training was due next. I used this formula for annual in column N.

=IF(M4="","",M4+DATE(1,1,-1))

M4 being the first cell with when the training was done.

Then i conditionally formatted the N column to do the following.

cell value less than =NOW()

Red (past due)

Cell value between =NOW() and =NOW()+30

Yellow (coming up within 30 days)

Cell value between =NOW()+31 and =NOW()+60

Orange (coming up within 60 days)

Hope this helps.

**colinmarcum** · 08-28-2013, 05:12 PM

Does that mean I need establish a due date for each individual training requirement?

Capture.PNG

From the picture we have 30 personnel that each have to do 13 different annual training requirements. And since the due date is based on a year after the course was taken that means a 390 different due dates. I was thinking =TODAY-L3()<365 then it is green; =TODAY-L3()<335 then it is amber; and so on.

I keep getting confused on if a past date is a negative value; thus subtracting it from TODAY's would make it a positive.

**FDibbins** · 08-28-2013, 05:22 PM

Originally Posted by colinmarcum

Does that mean I need establish a due date for each individual training certificate?

If you are setting up to check when they need to be renewed, I would have thought this would be self-evident? If you dont know the due date (or at least have the date last updated, and the certificate duration - 6 mths, 12 mths etc) then how do you know if it expires?

On a side-note to Liz, you can use TODAY() instead of NOW() - NOW() includes the time, which is not needed here. Also, if you will be using this in a LOT of formulas, it will speed things up if you put =TODAY() in it's own cell and then reference that cell in formulas. TODAY() and NOW() are volatile functions, which means they update EVERY time anything is updated in the workbook...pretty much every timer you enter a value/text

**colinmarcum** · 08-28-2013, 05:31 PM

I know it is exactly 365 days from the date posted in the cell. The problem is that if I establish a due date for each individual cell, and generate a rules for each due date that means I am generating three different rules for 390 different due dates. I am trying to find a formula based on the difference between the posted date and the TODAY date. >365 days (RED), 365 to 335 days (AMBER), <335 days (GREEN)

Just trying to find three formulas that I can copy+paste formatting to every cell.

**FDibbins** · 08-28-2013, 05:33 PM

you will need 3 different rules anyway, but you will be comparing today's date against when they were last updated.

Also, Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you

**colinmarcum** · 08-28-2013, 06:10 PM

I know I will need three rules "Just trying to find three formulas that i can copy+paste formatting to every cell."

I don't want 3x390 different rules that I have to type.

C BTRY Annual Training Requirements (28AUG13).xlsx

Here is the workbook from the picture. All the certifications I have posted are up to date so they would all be GREEN, but as JAN 14 starts coming around then those JAN 13 dates should start turning YELLOW, then when they pass turn RED.

**liz5818** · 08-29-2013, 09:12 AM

Colin, for general purposes, you would need to put the formulas into the conditional formatting in order to get the color, otherwise it would mess up your data in the cells. Then you apply the conditional formatting to the entire column. 3 rules/formulas being typed. That's it. period.
The only way i can see you getting what you are asking for specifically would be creating a vba to do it automatically, but if you have that many cells, it may take a while and would have to run every time you open the workbook or worksheet.
Also, setting up the expiration/renewal date, you put in the first formula and drag it down. I'm not sure why you think you need to type over 1000 formulas. this would then auto-generate when you put in the updated certification date.

fdibbins: TODAY() would work, but when researching i found the NOW() formula and it worked for what i needed.

**liz5818** · 08-29-2013, 09:13 AM

Duplicate - due to Forum glitch

**FDibbins** · 08-29-2013, 02:12 PM

yes the server has been acting up a bit today

Expiration based coloring, based on when training was done

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Expiration based coloring, based on when training was done

Re: Expiration based coloring, based on when training was done

Re: Expiration based coloring, based on when training was done

Re: Expiration based coloring, based on when training was done

Re: Expiration based coloring, based on when training was done

Re: Expiration based coloring, based on when training was done

Re: Expiration based coloring, based on when training was done

Re: Expiration based coloring, based on when training was done

Re: Expiration based coloring, based on when training was done

Re: Expiration based coloring, based on when training was done

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Beginning of Year Numbers Based on Expiration

3 Color Conditional Formatting Based on Various Expiration Dates

Classroom-based training?

Macro - schedule people for training based off their schedule and available training

Expiration date based on 5 years and Birth month,day ...

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions