How to set up formula for an inspection schedule?

  • Thread starter Thread starter G.T.W.
  • Start date Start date
G

G.T.W.

I have a 'create a shop' assignment that involves semi-annual
inspection requirement. There are 17 departments with inventory.
To this point, I have been pecking my way through, adding the
database( quantity, catagory by department, cost, and depreciation ).
My formulas have thus far been either simple addition or
multiplication. I have no idea how to figure a semi-annual
inspection schedule for the inventory for all the departments, spread
equally throughout the year. Could someone please pass me a clue?
Thanks
 
Hi,
Here is a simple method.
I'll assume your table has a header row and the four columns mentioned.
In cell E1 enter Recall Date
In cell e2 enter =max(e2:iv2)
In cell f2 enter a date for the first scheduled recall.
Leave g2 blank.
I cell h2 enter =IF(g2="x",f5+180,"")
Select cells e2:h2 and do a fill down to match your list.

It works like this.
When an item gets inspected, enter an x in g2 and the formula will create a
new recall date six months in the future and keep a history of inspection
dates.
You can copy and paste the formula in h2 into every other column to add to
the history.
The recall date will always show the highest date value in the row.
 
Thank you John. That worked after I figured out how to get the date
properly displayed in the cell. For some reason, any attempt to have
the date added to that 'IF" statement resulted in a very large number,
instead of a projected calendar date in the futer. By itself, the
date would display, but add the IF portion of the formula, and Excel
didn't know what to do. After using the View Function to have the
DATE _00_Jan_00_ added to the equation option, the projected date
would then appear correctly. Thank you very much!
 
Back
Top