Hour registration in Excel with calculation of time within intervals

If you work from 20:15 to 02:30 on a Saturday, how many hours are in the interval 15:30 to 00:00, and what should you be paid? The function HoursInInterval() can give you the answer and calculate your wage correct depending on it’s a weekday, weekend or a holiday.

You can download the workbook at the bottom of this article.

Example formulas

=@HoursInInterval($D3;$E3;16;18;$C3;MonThu)-IF(H3="x";Lunch;0)

• You will find this formula in cell I3 in sheet “Hour Registration”.
• $D3 is when you startet work. This parameter is mandatory.
• $E3 is when you stopped work. This parameter is mandatory.
• 16 is the start of the time interval we compare the worked time with. Optional parameter.
• 18 if the end of the time interval we compare the worked time with. Optional parameter.
• $C3 is the date. Optional parameter. Required for the Holiday handling to kick in.
• MonTue is the day code. If sent as a parameter, the day of the week must match with this code. You find the day codes in the sheet “Holidays”. Optional parameter. If omitted the function will calculate the hours regardless of type of day.

The last part -IF(H3=”x”;Lunch;0) subtracts the lunch time if H3 is “x”

Function HoursInInterval code

Function HoursInInterval(dFrom As Double, dTo As Double, Optional IntervalStart As Double, Optional IntervalStop As Double, Optional dDate As Date, Optional DayNum As Integer)
    
    Dim x As Long
    Dim FromTime As Double
    Dim ToTime As Double
    Dim dStart As Double
    Dim dStop As Double
    Dim Min As Long
    Dim iDay As Integer
    Dim OverMid As Boolean
    Dim Modus As Integer
 
    'Find weekday number
    If dDate <> "00:00:00" Then
        iDay = Weekday(dDate, vbMonday)
    End If
 
    'Is this a holiday?
    If IsHoliDay(dDate) = True Then
        iDay = 10
    End If
  
    'Find minute value between dFrom and dTo
    FromTime = Hour(dFrom) * 60 + Minute(dFrom)
    ToTime = Hour(dTo) * 60 + Minute(dTo)
 
    'If no time, exit with zero
    If FromTime = 0 And ToTime = 0 Then
        HoursInInterval = 0
        Exit Function
    End If
 
    'Find time interval
    dStart = IntervalStart * 60
    dStop = IntervalStop * 60
 
    'Increase with 24 hours if dStop < dStart
    If dStop < dStart Then
        dStop = dStop + 24 * 60
    End If
 
    'Increase with 24 hours if Totime <= Fromtime
    If ToTime <= FromTime Then
        ToTime = ToTime + 24 * 60
    End If
 
    'Check if ouside interval
    'If interval ends before FromTime, exit with zero
    If dStop < FromTime And dStop <> 0 Then
        HoursInInterval = 0
        Exit Function
    End If
 
    'The six situations
 
    If dStop = 0 And dStart = 0 Then
        Modus = 1
    ElseIf ToTime <= dStart Then
        Modus = 6
    ElseIf FromTime <= dStart And ToTime <= dStop Then
        Modus = 2
    ElseIf FromTime <= dStart And ToTime > dStop Then
        Modus = 3
    ElseIf FromTime > dStart And ToTime <= dStop Then
        Modus = 4
    ElseIf FromTime > dStart And ToTime > dStop Then
        Modus = 5
    End If
   
  
    'Modus=1 - no interval check
    If Modus = 1 Then
        Min = ToTime - FromTime
    End If
  
    'Modus=2 FromTime<=StartTime and Totime<=dStop
    If Modus = 2 Then
        Min = ToTime - dStart
    End If
  
    'Modus=3 FromTime<=dStart og ToTime>dStop
    If Modus = 3 Then
        Min = dStop - dStart
    End If
 
    'Modus=4 FromTime>dStart and ToTime<=sStop
    If Modus = 4 Then
        Min = ToTime - FromTime
    End If
 
    'Modus=5 FromTime>dStart og ToTime>dStop
    If Modus = 5 Then
        Min = dStop - FromTime
    End If
 
    'ToTime < dStart in interval
    If Modus = 6 Then
        Min = 0
    End If

    'If weekday is sent as parameter, iDay must be være lik iDay / hverdag / helg
    If DayNum <> 0 Then
  
        'Monday to Thursday
        If DayNum = 11 Then
            If iDay > 4 Then
                Min = 0
            End If
        End If
  
        'Weekday
        If DayNum = 8 Then
            If iDay > 5 Then
                Min = 0
            End If
        End If
  
        'Weekend
        If DayNum = 9 Then
            If iDay < 6 Then
                Min = 0
            End If
        End If
  
        'If given weekday or holiday, the day must match
        If DayNum < 8 Or DayNum = 10 Then
            If iDay <> DayNum Then Min = 0
        End If

    End If
 
 
    HoursInInterval = Min / 60
 
End Function

Function IsHoliDay(dDate As Date)
    Dim x As Integer
    Dim Stopp As Boolean
    Dim sString As String
    Dim FormattedDate As String
    Static err As Boolean
    
    On Error GoTo ErrMsg
 
    Dim Found As Long
 
    Found = fR(shHolidays, "Date", Format(dDate, "yyyy-mm-dd"), True)
 
    If Found <> 0 Then
        IsHoliDay = True
    Else
        IsHoliDay = False
    End If
 
    Exit Function
 
ErrMsg:

    If err = False Then
        err = True
        MsgBox ("Doesn't work. Check that the sheet Holidays exists")
    
    End If
    
End Function

Public Function EasterDate(Yr As Integer) As Date

    Dim x As Integer
    x = (((255 - 11 * (Yr Mod 19)) - 21) Mod 30) + 21
    EasterDate = DateSerial(Yr, 3, 1) + x + (x > 48) + 6 - ((Yr + Yr \ 4 + x + (x > 48) + 1) Mod 7)
    
End Function

Download the workbook: Hour registration (1203 downloads )