excel time help needed please.

[helpimcrap]

hi, i'm really stumped on this one....

i'm trying to work out how many hours on a spreadsheet are worked at premium (unsociable hours) and how to work it out...

e.g.

working 00:00-08:00.... unsociable hours are 18:00-06:00.

how can i get it to work out that 00:00-06:00 will be at a premium but the last 2 hours wont be.

i've tried IF/AND statments to work it out, but i'm having trouble getting them to work because of the crossover of time at 00:00 which means that i cannot just say it in the easier format.

i don't have the formula to hand what i was trying to use, but i could get it to work in a fashion but i need to be able to put in any start or end time and have it work out the premium from that. there are workarounds that could be employed but it's something that's been niggling me and no one i know knows how to do it!

if anyone could point me in the right direction it would help. cheers.

 

[helpimcrap]

thanks for the help...

this is the formula i've been trying to use and it's not complete as it won't work out night shift premium yet (i did originally but this is one i've just knocked up so you can get an idea of what i'm after)...

=IF(AND(B11<TIME(18,0,0),C11<TIME(18,0,0),B11>TIME(6,0,0)),IF(B11<C11,C11-B11),IF(AND(C11>TIME(6,0,0),B11<TIME(18,0,0)),C11-TIME(18,0,0),IF(C11<B11,1,0)))

Times
07:00-15:00
15:00-23:00
23:00-07:00

Premium Times
18:00-06:00

these times need to be flexible so that if i need to change the times to 18:00-06:00 then it records 12 hours at premium etc...

 

[helpimcrap]

Don't hard code the 18:00 06:00 times - put them in cells in the workbook, name those cells and refer to them as I did in my formula.
Is it safe to assume that no one will work more than 12 hrs in a single shift?

just noticed I posted the wrong formula: Overtime in row 11 equals
=IF(A11<OvertimeEnd,MIN(B11,OvertimeEnd)-MAX(OvertimeStart,A11),B11-A11)
But subject to the limitation stated before.

i put them into this formula more as a show of what i was aiming at... on the actual s/s they are in cells and hidden.

thanks for the help, i'll have a look at that link too.

 

[helpimcrap]

still struggling.... anyone else?

i think i'm about to give up on and put it down as a no-go...

have broke it down into various segments in an attempt to get it to pick it up one at a time and then just sum it and set it to 0 certain cells if other values come through.