Calculating Start/End time between nigh shifts 24h

Gosa357

New Member
Joined
Dec 25, 2013
Messages
5
Hello everyone,

Please, can any of you help with following problem that I have.
Problem goes like this:

We have a person who work in 24/7 support. One person work passive 24 hour, but it 24 hours are divided in to next shifts: normal 06-18, over hours 18-22, night hours 22-06.
So if we have some one who is supporting client say from 17h till 23h, we have next results:

Name Start time End Time 06:00 - 18:00 18:00 - 22:00 22:00 - 06:00

John 17:00 23:00 1:00 4:00 1:00

Is it possible to in time range enter formula who subtracted end time from start time, but taking into account 24 hours?

Much appreciate any help
Thank you.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Yes, but my problem is that I do not know how to set formula so it take into account start/end time and compare to shifts in 24h.
 
Upvote 0





06:00-18:0018:00-22:0022:00-06:00
NameStart TimeEnd Time


John17:0023:00shoud be 1shoud be 4shoud be 1
Mike16:001:00shoud be 2shoud be 4Shoud be 3

<colgroup><col width="65"><col width="87"><col width="78"><col width="89"><col width="87"><col width="102"></colgroup> <tbody>
</tbody>


Maybe I set problem wrong. Here how it should look like.
 
Upvote 0
=IF(B3<C3;B3+1;B3)-C3
I can get hours that I person work, but how to insert into formula time range?
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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