sliding hours payment
 

For a medical team that is scheduled 24*7, each medic gets paid for the hours they work. Some hours however get paid extra on top of their hourly rate.
so, 0:00 - 06:00 have different uplifts then 06:00 - 08:00 and so forth.
I have A3 = shifthour-start B3 = shifthour end, C3 - shifthours total
(infact b3-c3)
then I have 24 columns with heading
d1 0:00 e1 01:00 f1 02:00
d2 01:00 e2 02:00 f2 03:00 and so forth.
all I want is to have line three populate with the minutes within that hour that was worked.

as example a medic worked from 0:40 -2:00.
cell d3 should get 0:20 (from 0:40-01:00)
cell e3 should get 1:00 (from 1:00-2:00)
cell d3 should get 0:00 (shift ended at 02:00)

I got it working in cell d3 by entering

+IF($AC12-$AB12=0;"";ABS(IF(AND($AB12=AT$9;$AB12AT$8;$AC12=A T$9;$AC12=AT$8);+$AC12-$AB12;0)+IF(AND($AB12=AT$8;$AC12=AT$8;$AB12=AT$9;$ AC12AT$9);$AC12-AT$8;0)+IF(AND($AB12AT$8;$AC12AT$9;$AB12=AT$9;$AC1 2=AT$8);AT$9-$AB12;0))+IF(AND($AB12=AT$8;$AC12=AT$9;$AB12=AT$9; $AC12=AT$8);+AT$9-AT$8;0))

where BC12 = a3
where AC12 = b3
where AT8 = d1
where AT9 = d2

I think this statement is a bit long (255 characters). is there shorter way?

Thanks

Leo

The shorter formula I got does work as well, except for column D where
0:00 start and 01:00 end are stored.

AND I figured this is still a bit too long (255 characters does not "copy-sheet" and needs "copy cells")

Thx
LEO

+IF(+IF($AB12AT$9;0;MAXA(AT$8;$AB12))=0;+AT$9-MAXA(+IF($AB12AT$9;0;MAXA(AT$8;$AB12));IF($AC12<A T$8;0;MINA($AC12;AT$9)));AT$9-IF($AB12AT$9;0;MAXA(AT$8;$AB12)))-(AT$9-MAXA(+IF($AB12AT$9;0;MAXA(AT$8;$AB12));IF($AC12<A T$8;0;MINA($AC12;AT$9))))

sliding hours payment
 

Check out my website: Hours affected by dates. It handles times spanning
midnight.
Please let me know if it suits?

Cheers

--
Rob van Gelder - http://www.vangelder.co.nz/


"leo" wrote in message
...

The shorter formula I got does work as well, except for column D where
0:00 start and 01:00 end are stored.

AND I figured this is still a bit too long (255 characters does not
"copy-sheet" and needs "copy cells")

Thx
LEO

+IF(+IF($AB12AT$9;0;MAXA(AT$8;$AB12))=0;+AT$9-MAXA(+IF($AB12AT$9;0;MAXA(AT$8;$AB12));IF($AC12<A T$8;0;MINA($AC12;AT$9)));AT$9-IF($AB12AT$9;0;MAXA(AT$8;$AB12)))-(AT$9-MAXA(+IF($AB12AT$9;0;MAXA(AT$8;$AB12));IF($AC12<A T$8;0;MINA($AC12;AT$9))))


--
leo

sliding hours payment
 

Based on your original cell references you could use this formula in D3
copied across row

=IF($A3<$B3;SUMPRODUCT(--(ABS((MOD(ROW(INDIRECT($A3*1440&":"&($B3+($B3<$A3) )*1440-1));1440)+0.5)/720-D1-D2)<D2-D1))/1440;"")

format as time


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=509616

Thanks for posting this. It realy seems a lot better then I had. however. somehow the copy paste in MY question got corrupted on the "" and "" signs.

I believe your answer did too.

Here's the formula I tried pasting;

=+IF(+IF(A3D2;0;MAXA(D1;A3))=0;+D2-MAXA(+IF(A3D2;0;MAXA(D1;A3));IF(B3<D1;0;MINA(B3;D 2)));D2-IF(A3D2;0;MAXA(D1;A3)))-(D2-MAXA(+IF(A3D2;0;MAXA(D1;A3));IF(B3<D1;0;MINA(B3;D 2))))+IF(A3=A3;+D$2-A3;+"0:00")

Could you post the "<" and/or "" somewhere in the formula too?
A minor change in settings is the 0.5 in the formula for me is 0,5 but I got that sorted. not the final formula result though.

thx

BTW, the preview again got rid of these <'s.(greater/smaller then signs)
I'll try again with immediat submittal.

Rob, Thanks for the link. (Nice wedding).

The solution suits for its functioning, it clearly manages the date -skip.
However it not suits the requirement I had.

My lines tackled it in 231 characters. yours were over 345.

When move/copy a sheet (to build next month from the template page) the requirement per cell is to have less characters or it will trunkate.

Now alternatively I coulds select all cells and copy into a blank worksheet, but I want inexperienced users to be selfsupporting.

I believe they can manage right clicking the tab and selecting move-copy.
I'm much more concerned that everyone will follow the ctrl-a, ctrl-c, ctrl-v action let alone to find out how to insert a worksheet.
Anyway. we're still working on this one.

sliding hours payment
 

Hi Leo, did you try my solution above - I believe it has 126 characters
:)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=509616

sliding hours payment
 

My routine is overkill. It likely handles scenarios your layout will never
have.
If daddy's handles your case, please use that.

--
Rob van Gelder - http://www.vangelder.co.nz/


"leo" wrote in message
...

Rob, Thanks for the link. (Nice wedding).

The solution suits for its functioning, it clearly manages the date
-skip.
However it not suits the requirement I had.

My lines tackled it in 231 characters. yours were over 345.

When move/copy a sheet (to build next month from the template page) the
requirement per cell is to have less characters or it will trunkate.

Now alternatively I coulds select all cells and copy into a blank
worksheet, but I want inexperienced users to be selfsupporting.

I believe they can manage right clicking the tab and selecting
move-copy.
I'm much more concerned that everyone will follow the ctrl-a, ctrl-c,
ctrl-v action let alone to find out how to insert a worksheet.
Anyway. we're still working on this one.


--
leo

YES, daddylong
I did try that and Wrote back yesterday I really liked the size but got problems getting it to work OK, since the formula's in the web-browser don't show any greater then smaller then signs also your and my difference in decimals: I use , (comma) where you use .(dot)
I have tried copying my formula's again, but again the < are blanked out.
Also in your formula there's two missing equations. -Look at the "(HERE)" s.

=IF($A3(HERE)$B3;SUMPRODUCT(--(ABS((MOD(ROW(INDIRECT($A3*1440&":"&($B3+($B3(HERE )$A3))*1440-1));1440)+0.(this is the comma/dot difference)5)/720-D1-D2)D2-D1))/1440;"")
I tried greater then in both, less then in both and the combinations.
Nothing worked (athough different results) #REF or #VALUE or even #####
(for negative time values).
I'd like to see what exatc sign was in there.

Thanks

Leo

daddylonglegs,

Sorry ! I'm new to the bulletin board.
I followed the thread.

That opened a new web-page holding the entire threat.

INCLUDING the < signs. I pasted it into my sheet and

BINGO !!!


GREAT HELP - Thanks


Leo

PS I noticed EXCEL takes a long time calculating this for a full month and a full day (24 ranges of 1hr) but that's only excel working and not me.

sliding hours payment
 

Hi Leo,

I'm relatively new to this forum myself and can't give you much advice
about copying formulas from here, sorry.

I'm glad you got it working. That's the shortest formula I had but
SUMPRODUCT may not always be the quickest as you noticed - here's an
alternative

=IF((D1<$A3)*($A3<$B3)*($B3<D2);$B3-$A3;MIN(D2-D1;IF(($A3<D1)+(D1<$B3)+($B3<$A3)=2;$B3-D1+($B3<D1);0)+IF(($A3<D2)+(D2<$B3)+($B3<$A3)=2;D2-$A3+(D2<$A3);0)))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=509616