Hey guys, I'm making a spreadsheet to track quarterly training for the folks i work with. some of this training has to be done every six months. here is my problem. how can i set a formula that will show when training is due. i.e. if i did training in july, i need this sheet to notify me that i have training coming up in december. is it possible? thanks!!!!

Relevance
• vbmica
Lv 7

You will want to learn about date functions and how Excel stores dates.

=DATE(year, month, day)

where year, month, and day are the date of the current training

So if a training was given on July 9, 2007

and you type the number of months until next training is need in cell B2

=DATE(2007,7+B2,9)

will give you the next date for training.

Usually I just do it the quick and nasty way by adding 180 to the date. =A1+180 will give you the approximate date you want.

I have a sheet that performs a similar function. When I make calls, I put the date it that person's column. And after 30, 60 or 90 days it turns a specific color, like red orange and yellow, so I can easily know if I haven't called one of my contacts in a while.

I use conditional formatting: in cell a1 (or any cell you chose) type =today() This will automatically update each time you open that workbook. That is your reference cell to determine when everyone needs their training. Then, in each person's row, put the date of their last training event. Click on format, conditional formatting and for condition 1 I put:

cell value is between =\$A\$1-30 and =\$A\$1-60 ( I have no formatting for under 30 days)

cell value is between =\$A\$30-61 and =\$A\$1-90

I have this formatted to turn yellow

cell value is less than =\$A\$1-91

I have this formatted to turn red.

Your formatting would be different since your training events and dates are different. All you would have to do is change the numbers slightly and it will work the way you want. I hope this helps. Let me know if you don't understand.

• acuna
Lv 4
5 years ago