# time elapsed

#### venus123

##### New Member
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

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### Gerald Higgins

##### Well-known Member
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.

#### venus123

##### New Member
 day shift start time finish time total Sat 1 12:30 14:00 90 Sat 2 16:30 19:00 90 Fri 1 11:00 15:00 150 Sat 2 16:00 21:00 240 Sat 1 10:00 13:00 120 Sat 1 09:00 13:00 180

<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

#### venus123

##### New Member
anyone can solve this problem??

#### venus123

##### New Member
anyone can solve this problem??

#### venus123

##### New Member
anyone can solve this problem??

#### WhoCanDo

##### Board Regular
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

Replies
0
Views
1K
Replies
3
Views
1K
Replies
0
Views
244
Replies
5
Views
610
Replies
6
Views
429

1,195,665
Messages
6,011,026
Members
441,579
Latest member
satishrazdhan

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

### Which adblocker are you using?

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

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