Hi all, 

A kind request from an experienced H&S Official but an unexperienced Excel user......

Does anyone have a Excel training matrix available for me to use which has conditional formatting applied resulting in a cell traffic light system being displayed e.g. the cell goes black when the training expiry date has been exceeded, amber when its due to expire in two months’ time, cell displays green if more than two months from expiry. 

I`m not that hot on Excel, I appreciate any downloadable templates and/or advice. 

Kind regards

Go into the down load section of Health & Safety for beginners, https://www.healthandsafetytips.co.uk/#

The problem with using excel for holding a training matrix is it is 2 dimensional. Ie hard to show training history. If say you have a row with the person name in it, then a column heading over two columns for the training activity. Under this the one of the two columns has the date the training was done and the next column has the calculated date next due. With me so far.

The problem then comes once the training has been refreshed, you have to overtype the previous date which is now lost for ever.

To do it though, do as I note above and in a cell at the top list a number of months or years as appropriate ( as a number)

In the calculated cell column, you just add the number of years noted at the top of the last done date. Say it is in years then if you use a formula like this one in C15

=IF(B15="R",0,IF(B15>=1,B15+(B$7*365),"")) 

If is and either or option ie true or false so this first asks if the cell B15 has an R in it for required ( ie first time training) if yes then it puts a value of zero in the cell. This will give the date 00/01/00 which is actually the zero day of Jan 1900. (yes you do want this see later). The next part asks another if question if B15 is greater than or equal to 1 (so anything other than nothing or an R) put in the cell the date the in B15 but add to it what is in B7 times 365 days ( you have to add x number of years as days – well it is easier this way you can add years). The dollar sign holds this cell reference when you copy it down. Ie you type the formula in C15 and when you copy it to C16 the B15’s change to B16’s but the reference to B7 remains the same (Nb B7 has the number of years in it). At the very end of the formula ther is a double set of quote marks with no gap, this is the or option ie Not 1 or more and not R it puts in blank (so you don’t get error note)

The easiest way is to try it.

At the very top of the sheet somewhere say D1   type =today()  and it will put todays date in the cell 

You next need to use conditional formatting on C15 to make the cell red if the due date in C15 is less than todays date in D1 or if the date is 00/01/00.

Easy eh

Try it, and pm if you get stuck

Chris