# Calculating Start/End time between nigh shifts 24h

#### Gosa357

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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### Bjarnehansen

##### New Member
=(endtime-starttime)+(endtime<starttime)

#### Bjarnehansen

##### New Member
Sorry.
=(endtime-starttime)+(endtime<starttime)

#### Bjarnehansen

##### New Member
it cut this of <starttime)

#### Bjarnehansen

##### New Member
Code:
``=(endtime-starttime)+(endtime<starttime)``

#### Gosa357

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

#### Gosa357

##### New Member
 06:00-18:00 18:00-22:00 22:00-06:00 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>
</tbody>

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

#### Gosa357

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

1,102,274
Messages
5,485,783
Members
407,515
Latest member
franjey

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...