Timesheet Calculations

Hi all,

I will be working on this myself and I would prefer to stay away from Macro's and VBA if possible.

A quick history:

I work for a service company where my staff can be working on multiple jobs per day (Field staff can be 5-10 - office staff 20+). Currently my CRM system has a time capture in it but is not sophisticated enough to talk to my other software packages, as such I have to do a lot of manual work arounds. I also have a new finance system about to be implemented which allows me to more accurately assign costs to individual jobs. A part of this is that I want to accurately record wage costs against those jobs as well.

I need to be able to separate their times between standard and overtime hours work each day (as well as weekend work).
Weekend work part is a little easier, as if the day is a saturday then the first 2 hours worked is 1.5x and the rest is 2x (fairly easy for the spreadsheet to work on), if it's a Sunday then straight 2x.
The next part is a little bit more difficult as I need to be able to calculate the employees total hours for the day on a running basis and once they have worked more than 7.6 hours in the day then overtime rates come into effect - against first 2 hours at 1.5x and all others at 2x. This is that part where I am struggling with.

Hopefully that explains what I am after enough. I have attached the layout of the report below with a fortnights hours for 1 employee which covers all the above but a Sunday.

Ta,

Matt

Hi Richard,

Sorry for the delay in response (first time in 3 weeks I have been able to get back here).
While the above works for the first line with the same date, all subsequent lines for the same date only product a zero (0) response.
Is there anyway to thus carry it down to continue the formulations for the same day?

Ta,