Time Sheet - Calculating overtime

[jeffcampbell]

I have one that has me really stumped. I know how to write a formula which would sort for example 13 hours in one day into normal time / time 1/2 and double time but have no idea how to sort segments of hours into appropriate coulombs.

What I mean is if showing for eg 4 jobs in a day - 1st one 3 hours 2nd job 4 hours third job 2 hours and 4th 3 hours - the first and second jobs would all be in normal time - third job would have 1 hour in normal and 1 in time 1/2 and so on.

I have attached one of our time sheets to better show what im trying to create. New time sheet.xlsx I have attempted to write formula for the Monday to auto calculate how many hours for each job. I’m trying to write a formula which would display it like the Tuesday is (which i entered manually)

The normal time hourse are 8 - time 1/2 are 2 then anything there after is double time.

Any help really appreciated - this is doing my head in.

Cheers, Jeff

[RudiS]

Hi,

I was interested in the formulas too for my own personal learning, so I took your post over to Eileen's Lounge where I am a member and posted it there. http://www.eileenslounge.com/viewtopic.php?f=27&t=16108
One of the moderators and MVP Hans, came up with a solution, though he claims it not so elegant

I have attached his solution below.

[ncmay]

Try the attached file.

Monday to Friday only, not sure what was to be done for the weekend.

[jeffcampbell]

Thanks Rudis - I was trying to do similar - I don't know how Hans got his head around.

I ended up using yours ncmay and filled in for the Saturdays and Sundays plus a 4 hr day on Friday.

Really appreciate both of your help - I usually figure something out with most excel problems - worst case google and see similar formula. This one has me completely stuck.

File attached will fully functioning week for all days - I'm a happy chappy

[ncmay]

To look at another type, go to:

Download Personnel_Timesheet_12_31_2013.