Formula Errors

How to Calculate Completion Date with Working Hours and Holidays in Excel

Maira Mehtab Sep 28, 2026 868 views

Question details

The user needs to calculate an accurate completion date and time using a start date and time, a specific task duration, defined daily working hours (8:30 AM to 7:00 PM), and excluding Sundays.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Project scheduling where tasks span multiple days and non-working hours, requiring unused hours from one day to roll over into the morning of the next valid working day.
Observed behavior: Standard date addition does not account for business hours or specific non-working days like Sundays, resulting in inaccurate completion times.

Before you start

Ensure that your start date and time cells are properly formatted as 'Custom: mm/dd/yyyy hh:mm AM/PM'. If you plan to use the VBA solution, verify that your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm).

Solution 1Recommended

Use a Custom VBA Function for Complex Schedule Rules

Creating a User Defined Function (UDF) via VBA is the most accurate method for carrying over unused hours into the next working day while skipping Sundays.

When business rules are complex—such as pausing time calculation exactly at 7:00 PM and resuming it precisely at 8:30 AM the next day—standard Excel formulas can become overwhelmingly long and prone to errors. A VBA script allows you to loop through the required duration hour by hour, natively skipping predefined holidays and weekends.

Open the VBA Editor

Press Alt + F11 on your keyboard to launch the Microsoft Visual Basic for Applications editor.

Insert a New Module

In the top menu, click 'Insert' and select 'Module'. This creates a blank canvas for your custom code.

Write the Custom Function

Write a VBA function that accepts the start time and duration as arguments. Use a loop to add time in increments (e.g., minutes), checking at each step if the time is between 8:30 AM and 7:00 PM, and if the current day is not a Sunday (Weekday(Date) <> 1).

Apply the Function in Your Worksheet

Save your VBA code, close the editor, and return to Excel. You can now use your custom function in a cell, exactly like a native formula (e.g., =CalculateCompletion(A2, B2)).

Macro Security Settings: You must enable macros in your Excel Trust Center settings for the custom function to run properly.

Solution 2

Combine WORKDAY.INTL and Time Formulas

If you cannot enable macros, use a nested combination of WORKDAY.INTL and time calculation formulas to approximate completion dates.

Calculate Project Schedules Easily in WPS Spreadsheet

WPS Spreadsheet fully supports advanced date and time functions, including WORKDAY.INTL, NETWORKDAYS, and complex nested arrays. You can seamlessly calculate business hours, skip customized holidays, and determine exact completion dates with an intuitive interface.

1. Open Your Schedule: Launch WPS Spreadsheet and open your existing project schedule or timesheet.
2. Select the Target Cell: Click the cell where you want the final completion date and time to be displayed.
3. Insert the Date Formula: Navigate to the Formulas tab, select 'Date & Time', and choose WORKDAY.INTL to factor in your Sunday-only holidays.
4. Format the Results: Press Ctrl+1 to open the Format Cells dialog, then choose a Custom format to ensure both the day and exact hour are visible.

Fully compatible with Microsoft Excel formulas (.xlsx)Built-in Date and Time functions for precise project schedulingFree, lightweight, and fast alternative for daily productivityIntuitive formatting tools for displaying custom dates and times

Frequently Asked Questions

How do I exclude specific public holidays in addition to Sundays?

You can add a list of public holiday dates in a separate column. Both the WORKDAY and WORKDAY.INTL functions have an optional 'holidays' argument. Simply highlight your list of holiday dates as the final argument in the formula.

Why does my time calculation return a #NUM! error?

This error usually occurs if your start date is invalid or if the time values result in a negative number. Ensure all dates are recognized by the software as date-values rather than plain text.

Does the standard WORKDAY function account for partial working hours?

No, the WORKDAY function only returns whole dates. To account for specific hours of the day (like 8:30 AM to 7:00 PM), you must manually extract and add a time fraction using formulas like MOD, or utilize a custom VBA script.

How can I format the cell to show both date and time?

Right-click the cell and select 'Format Cells'. Go to the 'Custom' category and type 'mm/dd/yyyy hh:mm AM/PM' in the Type box. Click OK to apply the formatting.

Excel & Data Problems