Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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)))) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can this adjusted GPA formual be accomplised? | Excel Discussion (Misc queries) | |||
adding rows of hours and minutes to get a total | Excel Worksheet Functions | |||
calculating hours | Excel Worksheet Functions | |||
calculate hours just can't figure it out | Excel Worksheet Functions | |||
Subtracting paid hours from unpaid hours | Excel Worksheet Functions |