Working hours

Description

In this article, we will consider how to calculate working hours between two dates. This information may be needed both for the analysis of the efficiency of the employees' work and for the calculation of the useful time spent in the case of a piecework payment system.

So, let's write our own function to help us count working hours and call it WORKHOURS.

Open the menu Service - Macro - Editor Visual Basic , insert the new module (menu Insert - Module) and copy the text of this function there:

Function WORKHOURS(StartDate As Variant, EndDate As Variant)
    Application.Volatile
    StartDate = StartDate.Address
    EndDate   = EndDate.Address
    
    ' working hours of the first day 
    WORKHOURS1 = Evaluate("IF(AND(DAY(" & StartDate & ")=DAY(" & _
    EndDate & "),MONTH(" & StartDate & ")=MONTH(" & EndDate & "))," & EndDate & _
    "-" & StartDate & "-IF(AND(HOUR(" & StartDate & ")<=13,HOUR(" & EndDate & _
    ")>=14),1/24,0),18/24-MOD(" & StartDate & ",1)-IF(HOUR(" & StartDate & _
    ")<=13,1/24,0))")
    
    ' working hours of intermediate days 
    WORKHOURS2 = Evaluate("MAX((IF(AND((WEEKDAY(" & EndDate & ",2))"&"<"&"(WEEKDAY(" & StartDate & _
    ",2)),((WEEKDAY(" & StartDate & ",2))-(WEEKDAY(" & EndDate & ",2)))>1),(((DATEDIF(" & StartDate & _
    "," & EndDate & "," & """D""" & ")+1))-(FLOOR((DATEDIF(" & StartDate & "," & EndDate & _
    "," & """D""" & ")+1)/7,1)*2)-2),(((DATEDIF(" & StartDate & "," & EndDate & "," & """D""" & ")+1))-(FLOOR((DATEDIF(" & _
    StartDate & "," & EndDate & "," & """D""" & ")+1)/7,1)*2)))-2),0)*8/24")
    
    ' working hours of the last day 
    WORKHOURS3 = Evaluate("IF(AND(DAY(" & StartDate & _
    ")=DAY(" & EndDate & "),MONTH(" & StartDate & ")=MONTH(" & EndDate & ")),0,MOD(" & EndDate & _
    ",1)-9/24-IF(HOUR(" & EndDate & ")>=14,1/24,0))")
    
    WORKHOURS = WORKHOURS1 + WORKHOURS2 + WORKHOURS3
End Function

Close the Visual Basic editor and return to Excel.

Now in Function masters in the category User defined you can find our WORKHOURS function and use it. Function syntax following:

 =WORKHOURS(start_date; end_date)