Excel Spreadsheet formulas. need help?

Question

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!!!!

Answer 1

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.

Answer 2

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.

Answer 3

i'm no longer particular how your spreadsheet is determined out yet assuming you've your 12 months's revenues on one spreadsheet you should have January - December as column headings with archives below. So assuming you've significant identify in row a million and column headings in row 2, your figures could commence in row 3. once you've days vertically down column a, your figures might want to be, say B3:B31 for January, C3:C31 Feb etc. All you want do in b32 spotlight cells B3-B32 and tap the autosum key (?) then reflect the formulation for the era of. once you've all of your figures in only one column then basically spotlight Septembers figures and do an autosum or you may both kind or filter out (by ability of archives menu when you're pre 2007), for September and then basically upload up those figures, or in case you kind by ability of month, you may do a subtotal of each month with grand finished on the end.

Answer 4

If you are just setting a a schedule with alerts based on a repeating time frame ..use Outlook to manage it. You can set up many schedules based on repeating by days, weeks or years. I use mine for many things from reminders that monthly bills are due to birthdays to business trade shows, to client follow ups.

Go in to Outlook and schedule an appointment and use re-occurring button to schedule the time-frame.