sliding hours payment

leo · 02-07-2006, 08:40 PM

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=AT$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;$AC12=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

--
leo

leo · 02-07-2006, 08:40 PM

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($AB12>AT$9;0;MAXA(AT$8;$AB12))=0;+AT$9-MAXA(+IF($AB12>AT$9;0;MAXA(AT$8;$AB12));IF($AC12<AT$8;0;MINA($AC12;AT$9)));AT$9-IF($AB12>AT$9;0;MAXA(AT$8;$AB12)))-(AT$9-MAXA(+IF($AB12>AT$9;0;MAXA(AT$8;$AB12));IF($AC12<AT$8;0;MINA($AC12;AT$9))))

--
leo

Rob van Gelder · 02-08-2006, 01:30 AM

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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($AB12>AT$9;0;MAXA(AT$8;$AB12))=0;+AT$9-MAXA(+IF($AB12>AT$9;0;MAXA(AT$8;$AB12));IF($AC12<AT$8;0;MINA($AC12;AT$9)));AT$9-IF($AB12>AT$9;0;MAXA(AT$8;$AB12)))-(AT$9-MAXA(+IF($AB12>AT$9;0;MAXA(AT$8;$AB12));IF($AC12<AT$8;0;MINA($AC12;AT$9))))
>
>
> --
> leo

**daddylonglegs** · 02-08-2006, 04:21 PM

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

leo · 02-09-2006, 02:45 PM

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(A3>D2;0;MAXA(D1;A3))=0;+D2-MAXA(+IF(A3>D2;0;MAXA(D1;A3));IF(B3<D1;0;MINA(B3;D2)));D2-IF(A3>D2;0;MAXA(D1;A3)))-(D2-MAXA(+IF(A3>D2;0;MAXA(D1;A3));IF(B3<D1;0;MINA(B3;D2))))+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.

--
leo

leo · 02-09-2006, 02:45 PM

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

**daddylonglegs** · 02-09-2006, 06:01 PM

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

Rob van Gelder · 02-10-2006, 12:55 AM

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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

leo · 02-10-2006, 08:40 AM

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

--
leo

leo · 02-10-2006, 02:45 PM

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.

--
leo

**daddylonglegs** · 02-10-2006, 06:13 PM

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)))