TOP
# 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

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) `

- 1. What is VBA, basic concepts.
- 2. Debugger (Debugger)
- 3. Working with sheets (Sheets)
- 4. Working with cells (Ranges)
- 5. Properties (Properties)
- 6.1. Data types (Variables)
- 6.2. Data Types (Continued)
- 7.1. Conditions (Conditions)
- 7.2. Terms (Continued)
- 8.1. Loops (Loops)
- 8.2. Cycles (Continued)
- 9. Procedures and functions
- 10. Dialog windows
- 11.1. Workbook events
- 11.2. Worksheet events
- 12.1. Custom forms
- 12.2. Controls (Controls)
- 12.3. Control elements (Continued)
- 12.4. Control elements (Exercises)
- 13.1. Arrays (Arrays)
- 13.2. Arrays (Continued)
- 13.3. Arrays (Exercises)
- 14.1. Using Excel functions
- 14.2. Creating a custom function

- 1. SQL language, basic concepts.
- 2. Data selection (SELECT)
- 3. Data sorting (ORDER BY)
- 4. Data filtering (WHERE)
- 5. Use of metacharacters (LIKE)
- 6. Calculation (calculation) fields
- 7. Data processing functions
- 8. Data grouping (GROUP BY)
- 9. Sub-requests
- 10. Combination of tables (INNER JOIN)
- 11. OUTER JOIN
- 12. Combined requests (UNION)
- 13. Adding data (INSERT INTO)
- 14. Creating tables (CREATE TABLE)

- Import of currency rates from the NBU website
- Sum written in English
- Unpivottables (Unpivot)
- Function
Google Translate - Camel Case Function
- Snake Case Function
- Kebab Case Function
- Break Case Function
- Sentence Case Function
- Title Case Function
Fuzzy Lookup Function- Function GETSUBSTR
- Free extension "YouLibreCalc"
- Professional Extension "YLC Utilities"