Function for finding weekly average hours based on date

I have the following sheet that I use to maintain maintenance data. I have an "Average weekly hours" column and I would like to write a formula to calculate the "Average weekly hours" based on the "Last PM date" and "Running Hours". The data is used to know how much our machines are running and when maintenance will be due on this equipment. "Last PM date" is the last time the equipment was serviced and the "Running Hours" are the hours that it has been running since the last service. I would like to put in a formula so I don't have to manually do it for each unit of equipment.

Ex:

Unit # Last PM Date Last PM hours Last PM type Wkly hours date Running hours Avg Wkly hours

How about an example sheet?

I have attached a spreadsheet to show you what I mean.

So essentially I would like the formula to read the "Last PM date" calculate how many weeks it has been since that date and then divide the "running hours" by the number of weeks that have passed.

Thank you for getting back to me so promptly. Each row represents a different piece of equipment. I am not sure why in the formula you would do J2-J3 because each "Last PM date" needs to be compared to the present date. So for unit #1906 it would be correct that it was serviced 14 weeks ago. For unit #1907 the weeks should have been roughly 16 weeks, because the date for that unit, 1/24/13 was 16 weeks ago. Unit #1908 would have been roughly 20 weeks. Unit #1909 would have been roughly 46 weeks since it is almost a full year since the last service.

So with that being said, a formula that would calculate the amount of weeks that have passed from the "last service date" to the current date and then divide the "running hours" by the weeks that have passed is what I would want.

I hope that is a little clearer now. It isn't always the easiest to describe all of this via email. Please let me know if you have any more questions. I really appreciate your help. Thank you.

Hi PSA619,
Off subject what does the PSA stand for?

ok your issuse, in your example does this work?
H2

Please Login or Register  to view this content.

I2

Please Login or Register  to view this content.

Drag both rows down.

Nemo74,

Thank you that is great. PSA was my old job acronym. Doesn't mean anything special. Thank you for that function. I was basing all the weeks off the current day and last PM day so everyday it would change. So the today function was perfect.

What does the acronym stand for? Proffesonal service administrator?

Anyway if your issue is taken care of, please don't forget to mark your thread as solved.

Also if you like, feel free to give some rep buy clicking the * under ther persons name who helped you.


Have a super day!

PSA stands for Provider Services Analyst. Thanks again for your help! You have a good day too.