How to Calculate Excel Work Hours Only During Business Hours
Question details
The user wants to calculate the elapsed work time strictly between 9:00 AM and 5:00 PM on workdays, accounting for periods when the workbook is closed.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking continuous work hours or elapsed process time across multiple sessions while ignoring non-business hours, weekends, and holidays.
- Observed behavior
- VBA functions like Application.OnTime only run when Excel is actively open, making it impossible to actively track time in the background when the workbook is closed.
Create a separate worksheet named 'Holidays' containing a list of your company's official holiday dates so they can be accurately excluded from your elapsed business hours calculations.
Log Open/Close Timestamps with VBA and Calculate Retrospectively
Use Workbook event handlers to automatically log the exact times the file is opened and closed, then use formulas to calculate the elapsed business hours.
VBA code cannot execute while the Excel application is closed, which means functions like Application.OnTime cannot track time in the background. The most reliable workaround is to capture the exact timestamps when the workbook is opened and closed.
By saving these timestamps in a hidden sheet, you can later use date and time formulas to compute the hours that fall strictly within your 9:00 AM to 5:00 PM schedule, naturally excluding time when the file was closed or during the weekend.
Create a new worksheet in your workbook and name it 'TimeLog'. This sheet will act as a database to store the opening and closing timestamps.
Press ALT + F11 to open the VBA Editor. Double-click 'ThisWorkbook' in the Project Explorer. Create a Workbook_Open subroutine that finds the next empty row on the 'TimeLog' sheet and inputs the current timestamp using the Now() function.
In the same 'ThisWorkbook' module, create a Workbook_BeforeClose subroutine. This code should log the closing timestamp in the adjacent column of your 'TimeLog' sheet. Ensure you include a ThisWorkbook.Save command so the recorded times are not lost.
In a separate reporting sheet, use a combination of the NETWORKDAYS function and standard time formulas referencing the 'TimeLog' sheet. Calculate the difference between the open and close timestamps, restricting the valid time to > 9:00 AM and < 5:00 PM.
Track Business Hours Seamlessly with WPS Office
WPS Spreadsheet provides a robust environment for managing complex date and time calculations. With full support for VBA macros and advanced formulas, you can easily build automated time-tracking logs to measure elapsed business hours.
- 1. Open your workbook: Launch WPS Spreadsheet and open your existing time-tracking file.
- 2. Access the VBA Editor: Navigate to the 'Developer' tab on the ribbon and click 'VBA Editor' to insert your timestamp logging scripts.
- 3. Apply time calculations: Use built-in functions like NETWORKDAYS.INTL directly in your spreadsheet to exclude weekends and specified holidays from your time logs.
- 4. Save with Macros: Go to Menu > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' to ensure your tracking scripts are preserved.
Frequently Asked Questions
Why doesn't Application.OnTime work when Excel is closed?
Application.OnTime is a macro method that relies on the Excel application engine running in the background. When the workbook or the Excel application is entirely closed, VBA execution stops completely, preventing the timer from running.
Can I exclude custom company holidays from the business hours calculation?
Yes. You can use the NETWORKDAYS or NETWORKDAYS.INTL function in your formula and reference a specific range of cells containing your custom holiday dates. The function will automatically subtract those dates from the total working days.
How do I hide the timestamp logging sheet from users?
You can right-click the 'TimeLog' sheet tab and select 'Hide'. For more security, open the VBA Editor, select the sheet in the Project window, go to the Properties pane, and change the 'Visible' property to '2 - xlSheetVeryHidden'. This prevents users from unhiding it via the standard Excel interface.
