Calculating Start/End time between nigh shifts 24h

[Gosa357]

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.

 

[Bjarnehansen]

=(endtime-starttime)+(endtime<starttime)

 

[Bjarnehansen]

Sorry.
=(endtime-starttime)+(endtime<starttime)

 

[Bjarnehansen]

=(endtime-starttime)+(endtime<starttime)

hope that works

 

[Bjarnehansen]

it cut this of <starttime)

 

[Bjarnehansen]

=(endtime-starttime)+(endtime<starttime)

 

[Gosa357]

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.

 

[Bjarnehansen]

Filinformation og download - GratisUpload.dk - test.xlsx
take a look in that sheet

 

[Gosa357]

Name	Start Time	End Time
John	17:00	23:00	shoud be 1	shoud be 4	shoud be 1
Mike	16:00	1:00	shoud be 2	shoud be 4	Shoud be 3

<colgroup><col width="65"><col width="87"><col width="78"><col width="89"><col width="87"><col width="102"></colgroup> <tbody>
[TD="width: 65"]


[/TD]
[TD="width: 87"]
[/TD]
[TD="width: 78"]
[/TD]
[TD="width: 89"] 06:00-18:00 [/TD]
[TD="width: 87"] 18:00-22:00 [/TD]
[TD="width: 102"] 22:00-06:00 [/TD]

</tbody>

Maybe I set problem wrong. Here how it should look like.

 

[Gosa357]

=IF(B3<C3;B3+1;B3)-C3
I can get hours that I person work, but how to insert into formula time range?