LinkBack URL
About LinkBacks
Good morning everyone, I need some help making a formula through excel to help with my bodyshop for insurance companies.
We need to figure out hours per day on a job. For instance if a new job comes in on a Monday with 20 hours, we know that we have 4 days to get the number to 5 hours per day. Simple math 20 / 5 =4. I have a pretty good spreadsheet now that does this for me, but I need to take it one step further. Instead of this formula working on 1 day and 24 hours, I need to break it down to individual hours. For instance, if the car came in on Monday at 8am, and left on Thursday at 3pm, I need to find out the hours per day per hour in a sense. one of our insurance companies breaks down the hours per day in a new way resulting in different calculations.
I attached my spreadsheet how it is now, with some data in it so you can see how it works. Maybe someone can point me in the direction of how to get this calculation one step further by inserting a clock in there somewhere. I have been trying to figure it out but have had no luck.
It goes like this, customer, kind of car, the dollar amount of damages, the day the car came in, the total hours on the job, and our expected completion date. the results, results as of today and final are all the answers to the formula questions. I don't usually put any information in those fields.
The day the car came in and the expected completion date are the two fields that need formulas broken down into hour of the day. If anyone has any questions please feel free to ask!
Any help would be greatly appreciated.
Thanks in advance!
Rick
Register To Reply
Bookmarks