time elapsed

venus123

New Member
Joined
Apr 12, 2013
Messages
5
Hi All..im struggling to get this problem solved, i have try in another site but no reply yet, could you guys help me in solving this problem :
1. everyday at shift 2 the recess time between 18.00-19.00
2. Sunday-Thursday at shift 1 the recess time 11.30-12.30 and Friday the the recess time 11.30-13.00 and saturday no recess time
3. so the result is the elapse time exclude the recess time

Waiting for your reply, Thanks in advance
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Hi, welcome to the board.

I'm afraid your request seems to vague to allow a precise answer.

What exactly do you want to do ?

Don't worry too much about explaining the background to your situation, just focus on describing what data you have, and what exactly you want to do with it. A worked example would probably be very helpful.
 
Upvote 0
day
shift
start timefinish timetotal
Sat
112:3014:0090
Sat216:30
19:00
90
Fri111:00
15:00150
Sat216:0021:00240
Sat
110:0013:00120
Sat
109:0013:00180

<colgroup><col style="width:48pt" span="2" width="64"> <col style="width:48pt" span="2" width="64"> <col style="width:48pt" width="64"> </colgroup><tbody>
</tbody>
1. everyday at shift 2 the recess time between 18.00-19.00
2. Sunday-Thursday at shift 1 the recess time 11.30-12.30 and Friday the the recess time 11.30-13.00 and saturday no recess time
3. so the result is the elapse time exclude the recess time

HI Geralds, thanks for replying me..this is my case..could you help me, before i got this code :
=E2-D2-MAX(0,MIN(E2,IF(C2=2,"19:00",IF(B2="Fri","13:00","12:30")))-MAX(D2,IF(C2=2,"18:00","11:30")))
but this item not working if i add saturday there is no recess time.. just work until 12:30 and the shift 2 start at 12.30.
hope you can get what i mean and able to help me. thanks in advance
 
Upvote 0
I multiplied your formula by 1440 (min per day) because I was not getting the answers you had and then added an extra if(B3<>"Sat", Hope it is what you want.

=(E2-D2-IF(B2<>"Sat",MAX(0,MIN(E2,IF(C2=2,"19:00",IF(AND(B2="Fri", B2<>"Sat"),"13:00","12:30")))-MAX(D2,IF(C2=2,"18:00","11:30"))),0))*1440
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top