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.

 

[Gosa357]

[=IF(B3<C3;B3+1;B3)-C3<c3;b3+1;b3)-c3
]

I can get hours that I person work, but how to insert into formula time range?


</c3;b3+1;b3)-c3>

 

[VBA Geek]

Option Explicit


Public Function WORKINGTIMES(ByVal StartHour As Double, ByVal EndTime As Double)






Dim NormalHours As Double: NormalHours = 0
Dim OverHours As Double: OverHours = 0
Dim NightHours As Double: NightHours = 0


With WorksheetFunction




If StartHour >= 0.25 And StartHour <= 0.9166666666667 Then
        If EndTime <= 0.75 And EndTime >= 0.25 Then
            NormalHours = EndTime - StartHour
        ElseIf EndTime > 0.75 Or EndTime < 0.25 Then
            NormalHours = .Max(0.75 - StartHour, 0)
        End If
    
    
    If EndTime >= 0.75 Or EndTime <= 0.25 Then
        If StartHour >= 0.75 Then
         OverHours = 0.916666666666667 - StartHour
           
           
        ElseIf StartHour < 0.75 Then
            If EndTime < 0.916666666666667 And EndTime > 0.25 Then
                OverHours = EndTime - 0.75
            ElseIf EndTime > 0.916666666666667 Or EndTime < 0.25 Then
                OverHours = 0.916666666666667 - 0.75
            
            End If
        End If
    End If
    
    
    If EndTime > 0.916666666666667 Then
        If StartHour < 0.916666666666667 Then
            NightHours = EndTime - 0.916666666666667
        Else
            NightHours = EndTime - StartHour
            Exit Function
        End If
    End If
    
    If EndTime < 0.25 Then
        If StartHour > 0.25 Then
        NightHours = (1 - 0.916666666666667) + EndTime
        ElseIf StartHour < 0.25 Then
            NightHours = EndTime - StartHour
            NormalHours = 0
            OverHours = 0
            Exit Function
        End If
    End If




Else


    If StartHour < 0.25 Then


        If EndTime < 0.25 Then
            NightHours = EndTime - StartHour
        Else
            
            If EndTime > 0.25 And EndTime < 0.75 Then
                NightHours = 0.25 - StartHour
                NormalHours = EndTime - 0.25
             ElseIf EndTime >= 0.75 And EndTime < 0.916666666666667 Then
                NightHours = 0.25 - StartHour
                NormalHours = 0.75 - 0.25
                OverHours = EndTime - 0.75
                
              ElseIf EndTime >= 0.916666666666667 Then
                NightHours = 0.25 - StartHour
                NormalHours = 0.75 - 0.25
                OverHours = 0.916666666666667 - 0.75
              
              
            End If
        End If
   
    ElseIf StartHour > 0.916666666666667 Then
    
        If EndTime < 0.916666666666667 Then
            If EndTime < 0.25 Then
                NightHours = (1 - StartHour) + EndTime
            ElseIf EndTime > 0.25 And EndTime <= 0.75 Then
                NightHours = 0.25 + (1 - 0.916666666666667)
                NormalHours = EndTime - 0.25
            ElseIf EndTime > 0.75 Then
                NightHours = 0.25 + (1 - 0.916666666666667)
                NormalHours = EndTime - 0.25
                OverHours = EndTime - 0.75
            End If
            
        ElseIf EndTime > 0.916666666666667 Then
            NightHours = EndTime - StartHour
       
        End If
      End If
        
End If
    
    


End With


WORKINGTIMES = Array(Format(NormalHours, "hh:mm"), Format(OverHours, "hh:mm"), Format(NightHours, "hh:mm"))


End Function

 

[VBA Geek]

How to use the above:

The function has two arguments, start time and end time.

Select all 3 cells in which you would like to return Normal Hours, Over Hours, and Night Hours. Then type Equal Sign and input the two argument into the function,

afterwards type CONTROL SHIFT ENTER and you should see in the order Normal Hours, Over Hours, Night Hours. I tested it on a few combinations and it worked.

Let me know if it helps.

Cheers

 

[Bjarnehansen]

Take look at this one:
it is a sheet to download

Filinformation og download - GratisUpload.dk - new Excel - arbejdstid version 1.0.6.xlsm