Time & a half calulated on real time (not after x many hours worked)

Hi

I would start with dates & times in their own columns. You are over complicating things as well, with overtime between 11:30 PM - 7:30 AM and regular time between 7:30 - 11:30 PM you will need a bunch of IF's, AND's OR's etc. Probably why your not getting much response to your post!

Here's a link to working with Dates and Times

Originally Posted by Kevin UK

Hi

I would start with dates & times in their own columns. You are over complicating things as well, with overtime between 11:30 PM - 7:30 AM and regular time between 7:30 - 11:30 PM you will need a bunch of IF's, AND's OR's etc. Probably why your not getting much response to your post!

Here's a link to working with Dates and Times

HI,

Thanks for replying Kevin.

I can't change the time bands (annoyingly) that is just the deal at work. Do you think it is just a little too tricky to do what I want?

Take your point about separate columns - however then you can't work out the possible bridge over two days, (ie Monday 22:00 - Tuesday 03:00 in separate columns returns a shift of -19 hrs).

Unless anyone can think of a workaround,even if slightly convoluted, maybe I'm stuck.

Any thoughts?

Nick

I can do convoluted

Formula:

=ROUNDUP(((D2-C2)+(MAX(0,"07:30"-MOD(C2,1))+MAX(0,-MOD(D2,1)+"7:30"))*0.5)*24*VLOOKUP(B2,$A$11:$B$13,2,0),2)

well fliberdigibbit! you got it it seems. though I don't really understand it... I'll have to look at the MAX and MOD functions.

Thank you very much.

N

You're welcome,

As an afterthought, I didn't check the results with a start time before 23:30 and finish after 07:30, I think it should be ok though.