How to calculate working hours per month in Excel?
In many organizations, employees follow a standard work schedule, such as8 hours per day and5 days per week. Accurately calculating the total working hours per month is essential for payroll processing, project tracking, and workforce management. However, manually tallying the total hours for each month can be error-prone and time-consuming, especially when you need to consider weekends, public holidays, or employees with varying schedules. Excel provides practical tools and methods to help you streamline and automate this calculation, ensuring precision and efficiency in your operations.
Below are several effective solutions for calculating monthly working hours in Excel, suitable for different data structures and needs:
Calculate total working hours per month with formulas
VBA Code – Calculate monthly working hours for multiple rows automatically
Calculate total working hours per month with formulas
Excel’s formula functions provide a straightforward and reliable approach to calculate total working hours per month when you know the start and end dates, and the daily working hour quota. This method works best when each day has a fixed work schedule, and you want to account for weekends and optionally holidays. Here is how you can set up your calculation:
1. Select a blank cell where you want to display the result, and enter the following formula:
=NETWORKDAYS(A2,B2) *8After typing the formula, press the Enter key. Initially, the result may appear formatted as a date. Below screenshot demonstrates this:
2. To display the value as a number (working hours), select the cell with the formula result. Then on the Home tab, find the Number Format dropdown and select General. The cell will now display the total number of working hours as a plain number, as shown:
Tips: To accurately reflect periods that include holidays, you can exclude those days by adding a holiday range in your formula. Use:
=NETWORKDAYS(A2,B2, C2:C4) *8where A2 is the start date, B2 is the end date, and C2:C4 lists holiday dates you wish to exclude. Format the cell to General as described above.
Note: Adjust the cell references (such as A2, B2, C2:C4) according to the location of your data. This method works best for standard daily working hours. If employees work part-time, or daily working hours vary, you may need to adapt the formula or use one of the alternative methods below.
One advantage of using formulas is that once set up, they update automatically when you modify the dates or holidays. However, for datasets containing multiple employees or more complex attendance records, you might find the following advanced solutions more convenient.
VBA Code – Calculate monthly working hours for multiple rows automatically
If your worksheet records multiple employees or rows—each with different periods or varying start and end dates—manually entering formulas row by row becomes tedious. Using a VBA macro allows you to automatically compute working hours for each row in bulk, saving time and reducing manual errors.
This solution is particularly useful when you have a table where each row represents an employee or a distinct work period, with columns for Start Date, End Date, and optionally Holidays. The macro below will loop through all the rows and fill in the calculated working hours for each entry.
1. Open the Visual Basic for Applications editor by clicking Developer Tools > Visual Basic. In the new window, click Insert > Module, and paste the following VBA code into the code area:
Sub CalculateMonthlyWorkingHours()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim startCol As String, endCol As String, holidayCol As String, resultCol As String
Dim holidays As Variant
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = Application.ActiveSheet
' Adjust these column letters as needed
startCol = "A" ' Start Date in Column A
endCol = "B" ' End Date in Column B
holidayCol = "C" ' Holiday list in Column C (optional; can be blank)
resultCol = "D" ' Results in Column D
lastRow = ws.Cells(ws.Rows.Count, startCol).End(xlUp).Row
For i = 2 To lastRow
If ws.Cells(i, startCol).Value <> "" And ws.Cells(i, endCol).Value <> "" Then
If ws.Cells(i, holidayCol).Value <> "" Then
holidays = Split(ws.Cells(i, holidayCol), ",")
ws.Cells(i, resultCol).Value = WorksheetFunction.NetworkDays(ws.Cells(i, startCol), ws.Cells(i, endCol), holidays) * 8
Else
ws.Cells(i, resultCol).Value = WorksheetFunction.NetworkDays(ws.Cells(i, startCol), ws.Cells(i, endCol)) * 8
End If
Else
ws.Cells(i, resultCol).Value = ""
End If
Next i
End SubNotes and instructions:
- Adjust the column letters (startCol, endCol, holidayCol, resultCol) as needed to match the layout of your worksheet.
- The holidayCol should contain dates separated by commas, such as 7/3/2025,7/18/2025. If not needed, you can leave it blank.
- This macro assumes data starts in row 2 (after the header).
2. To run the macro, click the 
button or press F5 while the module is selected. The code will automatically calculate and fill in the total monthly working hours for all rows in the result column. If you encounter an error, check that your data references and date formats are correct.
This method is especially effective for automating calculations across large employee rosters or multi-row timesheet data, ensuring consistency and reducing the risk of missed records. If your data structure is more flexible or you wish to summarize daily attendance logs, consider using a Pivot Table as described below.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in