time sheet question

[jonny_the_rod]

please could someone have a look at the attached, at the moment i print these out then split up the total hours per day. I write in how many are regular hours, 1.5hrs and 2x hrs with a pen. i'm hoping someone can help me to get it to do this automatically.

before 05:00, after 20:00, after 10hrs they are on 1.5hrs

sunday are all 2x hrs

so if someone mon - fri starts 04:00 and finishes at 18:00
total for that day 14hrs breaks down to 5hrs = time and a half and 9hr = regular

i hope that makes sence
thanks

[jwhitwell]

jonny,

In your example (in at 4, out at 18) - what's the logic that says they'd be only 9 hours regular? From the rest of your post, I'd have assumed it was 10 hours regular (in "before 05:00... after 10 hours they are on 1.5 hours").

[DonkeyOte]

Presumably Date relates to In time as opposed to Out Time ?
What happens if a shift crosses midnight say on Sunday -- is time up to Midnight paid at double time and then that time excluded from any time and a half calculation for the portion of time worked on Monday ?

Not a totally wasted post from me though... you could simplify your Total time formula I think to:

I11: =IF(COUNT($D11,$F11)<2,0,24*(MOD($F11-$D11,1)))

this would cater also for shifts crossing midnight.

[jonny_the_rod]

jonny,

In your example (in at 4, out at 18) - what's the logic that says they'd be only 9 hours regular? From the rest of your post, I'd have assumed it was 10 hours regular (in "before 05:00... after 10 hours they are on 1.5 hours").

its so stupid how they have me doing it:

i'll try to explain better between 20:00 & 05:00 is time and half. so if someone starts at 04:00 then for 1st hr they are on time and half then after 14:00 they'd be on time and half again because they'd of worked more than 10hrs in that day.

[jonny_the_rod]

Presumably Date relates to In time as opposed to Out Time ?
What happens if a shift crosses midnight say on Sunday -- is time up to Midnight paid at double time and then that time excluded from any time and a half calculation for the portion of time worked on Monday ?

if it crosses midnight say someone starts does 18:00 - 03:00 on sunday they be on double time for 6hrs till midnight then 3hrs 1.5x becuase its before 05:00

Not a totally wasted post from me though... you could simplify your Total time formula I think to:

I11: =IF(COUNT($D11,$F11)<2,0,24*(MOD($F11-$D11,1)))

this would cater also for shifts crossing midnight.

thanks i'll try this