# calculating time values

1. ## calculating time values

Hello

I have on situation.
one worker works 09:00 pm to 01:00 am
other 05:00 pm to 04:00 am
i have to pay about different time values:
06:00 pm to 10:00 pm 10% more than 50\$
10:00 pm to 06:00 am 20% more than 50\$

I want that excel calculate this if I give start time and end time

I hope You understand what I mean, because my english is not wery good.  Register To Reply

2. ## explain

I think I have to explain more what I need:

H.Gustavson start 09:00 pm and finish 01:00 am
06:00 pm to 10:00 pm 10% more than 50\$
10:00 pm to 06:00 am 20% more than 50\$

so

i have to pay 10% more 1 hour (09:00pm-10:00pm)
and 20% more 3hour(10:00pm-01:00am)
1x50\$x1,1=55\$
3x50\$x1,2=180\$
sum: 55+180=230\$
this is what I want that excel to if I give start and end time  Register To Reply

3. If start time of shift is in A2 and end time in B2 then use this formula in C2 to get "regular hours" (06:00 - 18:00)

=((A2>B2)*MEDIAN(0,B2-1/4,1/2)+MAX(0,MIN(3/4,B2+(A2>B2))-MAX(1/4,A2)))*24

Then in D2 for 10% hours (18:00 to 22:00)

=((A2>B2)*MEDIAN(0,B2-3/4,1/6)+MAX(0,MIN(11/12,B2+(A2>B2))-MAX(3/4,A2)))*24

and then for 20% hours in E2

=MOD(B2-A2,1)*24-C2-D2

format C2:E2 as number  Register To Reply

I tried this formula but i had an error (excel highlights in the first formula MEDIAN(0,B2-1/4,1/2)
can you please make this function on excel for me and upload the file here.

If i could make this formula work then its wery helpfull for me.  Register To Reply