# 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.

Help please

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

Please help

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

4. Thank you daddylonglegs,

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.

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1