How to Calculate Working Time in Hours and Minutes in Excel
Question details
The user needs to calculate the exact working or response time between two date-time stamps, excluding weekends, public holidays, and hours outside the standard 8:00 AM to 5:00 PM business schedule.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking employee hours, calculating helpdesk ticket response times, or measuring project duration strictly within standard business hours.
- Observed behavior
- Simple subtraction of two date-time values includes non-working hours, weekends, and holidays, resulting in an inaccurate representation of actual business hours worked.
Ensure your start and end date-time values are properly formatted as dates and times. Additionally, create a separate list of public holiday dates in your worksheet so the formula can reference and exclude them.
Use a LET and NETWORKDAYS.INTL Formula
Utilize a robust formula combining LET, NETWORKDAYS.INTL, and custom time formatting to accurately deduct weekends, holidays, and off-hours.
This method uses Excel's advanced LET function to define variables like holidays and applies conditional math to calculate the exact business hours elapsed. The NETWORKDAYS.INTL function effectively counts valid working days, adjusting for weekends and specified holidays.
Create a table or named range containing your public holiday dates. Name this range or reference it, for example, as 'Public_holidays[List Public holidays]'.
Select the cell where you want the result. Assuming A2 is the start time and B2 is the end time, input the formula: =LET(hol,Public_holidays[List Public holidays],IF(COUNT(A2:B2)<>2,"",TEXT(SUM(IF(AND(INT(A2)=INT(B2),WEEKDAY(A2,2)<6,OR(INT(A2)=hol)=FALSE),MAX(0,B2-A2),0),IF(AND(INT(A2)<>INT(B2),WEEKDAY(A2,2)<6,OR(INT(A2)=hol)=FALSE),MAX(0,17/24-(A2-INT(A2))),0),IF(AND(INT(A2)<>INT(B2),WEEKDAY(B2,2)<6,OR(INT(B2)=hol)=FALSE),MAX(0,B2-INT(B2)-8/24),0),MAX(0,(NETWORKDAYS.INTL(A2,B2,1,hol)-1)*9/24)),"[hh]:mm:ss")))
Right-click the result cell, select 'Format Cells', navigate to the 'Custom' category, and enter '[hh]:mm:ss'. This specific format prevents hours from resetting to zero after 24 hours.
Calculate Working Time Using a Custom VBA Function
Create a User Defined Function (UDF) via VBA to handle complex working hour calculations seamlessly, keeping your worksheet formulas clean.
Calculate Complex Working Hours Easily in WPS Spreadsheet
WPS Office provides a highly compatible and powerful Spreadsheet tool that perfectly supports advanced Excel functions like LET, NETWORKDAYS.INTL, and custom time formatting. It allows you to track response times and calculate exact working hours effortlessly.
- 1. Open WPS Spreadsheet: Launch WPS Spreadsheet and open your document containing the start and end date-time records.
- 2. Set Up Holiday Data: Enter your public holiday dates in a separate column and define them as a named range.
- 3. Apply the Formula: Paste the LET and NETWORKDAYS.INTL working hour calculation formula into your target cell.
- 4. Format Cell as Hours: Right-click the cell, click 'Format Cells', choose 'Custom', and apply the '[hh]:mm:ss' format to view total working hours accurately.
Frequently Asked Questions
Why does my calculated time display as a standard date instead of total hours?
If you see a date instead of hours, the cell formatting is incorrect. You need to apply custom formatting. Right-click the cell, select 'Format Cells', choose 'Custom', and type '[hh]:mm:ss'. The square brackets ensure that hours calculate beyond 24 without rolling over into days.
How do I change the working hours to 9:00 AM to 6:00 PM?
You can adjust the fractions in the formula representing the hours of the day. In the formula provided, change '8/24' (8:00 AM) to '9/24' (9:00 AM), and change '17/24' (5:00 PM) to '18/24' (6:00 PM). Also, ensure that the total daily working hours multiplier changes from '9/24' to '9/24' if the total hours remain 9, or adjust accordingly.
Does NETWORKDAYS.INTL work for non-standard weekend days?
Yes, NETWORKDAYS.INTL is highly flexible. The third argument in the function (which is set to '1' by default for Saturday and Sunday) allows you to specify exactly which days of the week are considered weekends, making it perfect for shift work or non-standard schedules.
