How to Get Work Hours Between Dates Custom Schedule in Excel

Why This Task Matters in Excel

Calculating the exact number of paid, billable, or productive hours that fall between two date-time stamps is a recurring need in payroll, project management, call-center analytics, manufacturing, and service industries. Many businesses do not run on the classic Monday-to-Friday, 09:00-17:00 schedule. Retail stores open on weekends, hospitals operate 24 × 7 but pay differentials for night shifts, and global teams often define “working days” to match regional holidays.

Being able to pull precise work hours out of a raw log means you can:

• Pay employees accurately—overtime penalties and weekend premiums depend on hours worked during specific windows.
• Invoice clients correctly—consultants frequently bill only the hours that overlap with the client’s accepted service window.
• Forecast capacity—knowing how many production hours remain before a deadline helps scheduling staff and machines.
• Maintain compliance—several jurisdictions mandate break durations and maximum weekly hours; calculations based on precise work periods are needed for audits.

Excel excels (pun intended) here because it stores dates as serial numbers, letting you treat date-time values as quantitative fields. Functions such as NETWORKDAYS.INTL can skip unwanted days automatically, while SUMPRODUCT, FILTER, LET, and LAMBDA allow fully customized “working calendars” without VBA. Mastering this technique ties into other Excel skills like dynamic arrays, custom holiday tables, and Power Query transformations. Without it, analysts risk manual errors, overpaying staff, incorrect project KPIs, or compliance penalties.

Best Excel Approach

The fastest, formula-only route for most schedules is to combine NETWORKDAYS.INTL (to count whole working days) with arithmetic that trims partial first and last days. In Office 365 or Excel 2021, wrapping the logic in LET makes it easy to maintain and much faster than row-by-row VBA loops.

Use this pattern when:

• Your schedule can be expressed as whole working days + a fixed number of hours per working day.
• You have a holiday list.
• You need a one-cell answer without helper columns.

Syntax (recommended):

=LET(
     Start, A2,                                  /* start date-time stamp */
     End,   B2,                                  /* end date-time stamp   */
     Hol,   Holidays,                            /* named range of dates */
     DayHrs, 8,                                  /* hours in a full day  */
     Pattern, "0000011",                         /* Sat-Sun off           */
     StartDate, INT(Start),
     EndDate,   INT(End),
     FullDays, MAX(0, NETWORKDAYS.INTL(StartDate+1, EndDate-1, Pattern, Hol)),
     FirstDayHours, MAX(0, MIN(TIME(17,0,0), MOD(End,1)) - MAX(TIME(9,0,0), MOD(Start,1))) * 24,
     LastDayHours,  IF(StartDate = EndDate, 0, MAX(0, MIN(TIME(17,0,0), MOD(End,1)) - TIME(9,0,0)) * 24),
     Total, FullDays*DayHrs + FirstDayHours + LastDayHours,
     Total
)

Alternative (for dynamic shift tables or non-fixed daily hours):

=SUMPRODUCT(
    (--(ISNUMBER(MATCH(SEQUENCE(EndDate-StartDate+1,1,StartDate), ShiftTable[Date],0)))),
    ShiftTable[WorkHours]
)

Parameters and Inputs

• Start and End: TRUE date-time stamps. Excel stores 2023-05-15 14:30 as a single number; both parts must be present.
• Pattern: A seven-character text string for NETWORKDAYS.INTL where “1” means weekend/closed and “0” means working day, starting with Monday. For a Thursday-Monday retail week (closed Tuesday and Wednesday) use \"1100001\".
• DayHrs: Regular shift length in decimal hours—8 for 08:00-17:00 with a 1-hour unpaid lunch.
• Holidays: A vertical list of full-day dates formatted as dates, not text.
• ShiftTable (alternative method): A structured table listing every date in the calendar with a column of scheduled hours. Allows 0, 4, 6, 8, 10, or night-shift fractional entries.
• All time values must use Excel’s time format (e.g., 17:00:00). Avoid text like \"5pm\".
• Edge cases: Start after end (return zero or error), null timestamps, shifts crossing midnight—use IFERROR, MAX(0, …), or a second formula pass to resolve.

Step-by-Step Examples

Example 1: Basic Scenario

Situation: You run a small consultancy that works Monday to Friday, 09:00-17:00, closed on public holidays. You log a support ticket opened on 2023-05-12 15:15 and closed on 2023-05-16 10:45. You want the chargeable hours.

1. Enter the start stamp in [A2]: 2023-05-12 15:15.
2. Enter the end stamp in [B2]: 2023-05-16 10:45.
3. Create a named range Holidays containing [2023-05-15] (Assume May 15 is a holiday).
4. In [C2] paste the recommended LET formula.
5. Evaluate:

• StartDate = 2023-05-12 (Friday).
• EndDate = 2023-05-16 (Tuesday).
• FullDays = NETWORKDAYS.INTL(2023-05-13, 2023-05-15, \"0000011\", Holidays) returns 0 because Saturday, Sunday, and Monday are not valid workdays.
• FirstDayHours: End of first day is the normal shift end 17:00 minus start time 15:15 = 1 hour 45 minutes = 1.75 hours.
• LastDayHours: Because StartDate differs from EndDate, last day uses end time 10:45 minus regular start 09:00 = 1 hour 45 minutes.
• Total = 0 × 8 + 1.75 + 1.75 = 3.5 hours.

Why it works: We excluded weekend days automatically, skipped the Monday holiday, and trimmed partial first/last days precisely.

Common variations

• Ticket spans a single day → formula collapses LastDayHours to 0 and returns only FirstDayHours.
• Start earlier than 09:00 → MAX clamps to 09:00 so unpaid early arrival hours are ignored.
• End after 17:00 → MIN clamps to 17:00, discarding non-billable evening time.

Troubleshooting
If the formula returns negative hours, confirm the start timestamp precedes the end timestamp. Wrap the final Total in ABS or return an error with:

=IF(End<Start,"Start after end",Total)

Example 2: Real-World Application

Scenario: A hospital operates three shifts every day:

• Shift A – 07:00-15:00
• Shift B – 15:00-23:00
• Shift C – 23:00-07:00 next day

You need to calculate the number of Shift B hours a nurse worked between two timestamps for night differential pay. Because the hospital never “closes,” NETWORKDAYS can’t help. A helper table is more appropriate.

1. Build a table named ShiftCalendar with columns: Date, Shift, StartTime, EndTime, Hours. Populate rows for the full year with Power Query or SEQUENCE. Example:

• 2023-05-10, B, 15:00, 23:00, 8
• 2023-05-11, B, 15:00, 23:00, 8

2. In cell [A2] enter 2023-05-10 20:00.
3. In cell [B2] enter 2023-05-11 18:30.
4. Create a calculated column ActualStart in ShiftCalendar:

=[@Date] + [@StartTime]

5. Create ActualEnd:

=IF([@EndTime] < [@StartTime], [@Date] + 1 + [@EndTime], [@Date] + [@EndTime])

6. Now compute shift B overlap hours in cell [C2]:

=SUMPRODUCT(
   (--(ShiftCalendar[Shift]="B")),
   (MAX(0,
     MIN($B$2, ShiftCalendar[ActualEnd]) -
     MAX($A$2, ShiftCalendar[ActualStart])
   )) * 24
)

Explanation

• The MAX/MIN pair finds the overlapping segment between the worker’s interval and each shift block.
• Negative results (no overlap) are pushed to zero by MAX(0, …).
• Multiplying by 24 converts days to hours.

Business gain: HR obtains the exact 5.5 hours the nurse spent in Shift B (3 h on day 1 plus 2.5 h on day 2). The same calendar can power dashboards, overtime alerts, and labor-law compliance checks.

Integration

• PivotTable on ShiftCalendar for totals.
• Conditional formatting to highlight understaffed shifts.
• Power Query refresh to keep next year’s calendar current.

Performance
With a full year × 3 shifts = 1,095 rows, SUMPRODUCT is instantaneous. For multi-year, use FILTER to pre-limit rows inside LET to the date range before passing to SUMPRODUCT, improving speed dramatically.

Example 3: Advanced Technique

Edge Case: A global SaaS company supports customers from 06:00 Monday in Sydney (GMT +10) to 18:00 Friday in San Francisco (GMT −8). Local holidays vary by region. The SLA counts only “global support hours,” which correspond to weekdays 00:00-24:00 UTC. You receive UTC timestamps and must subtract hours that fall on public holidays in any region.

1. Build a Holidays table with Region and Date columns. To get a combined list of all holiday dates worldwide, create a distinct list in [Holidays_All] using UNIQUE or Power Query.
2. Because any holiday pauses the clock, the schedule effectively becomes a regular 5-day workweek but with an expanded holiday list.
3. For timestamps in [A2] (start) and [B2] (end) and the holiday list in [Holidays_All], use:

=LET(
  sDate, INT(A2),
  eDate, INT(B2),
  Full, MAX(0, NETWORKDAYS(sDate+1, eDate-1, Holidays_All)),
  First, (eDate > sDate) * (1 - MOD(A2,1)),   /* hours left in first day */
  Last,  MOD(B2,1),                            /* hours in last day      */
  Total, (Full*24) + (First*24) + (Last*24),
  Total
)

4. The formula expands to nearly any time zone because you unify everything in UTC first.

5. Edge-testing with intervals that span daylight-saving changes is unnecessary in UTC.

Professional tips

• Keep the holiday list dynamic via Power Query connecting to an online calendar.
• If regions add holidays mid-year, refreshing instantly updates every SLA calculation in the workbook.

Tips and Best Practices

1. Name things: Use named ranges (Holidays, DayHrs, Pattern) or structured tables for clarity and reduced error risk.
2. Clamp times: Always apply MAX and MIN on partial days to avoid negative overlaps when start or end are outside shift hours.
3. Dynamic arrays save helpers: Office 365’s SEQUENCE, FILTER, and UNIQUE can generate calendars on the fly without VBA.
4. Pre-filter rows for speed: When using SUMPRODUCT on large shift tables, wrap FILTER to include only rows within Start-End. This can slash calculation time.
5. Document the pattern: Keep a legend near the formula explaining \"0000011 = Sat-Sun off\". Future editors will thank you.
6. Audit with test cases: Maintain a worksheet of known intervals and expected results to catch accidental formula changes.

Common Mistakes to Avoid

1. Using text dates: If \"2023/05/12\" is stored as text, NETWORKDAYS.INTL returns #VALUE!. Convert with DATEVALUE or set the worksheet to proper date format.
2. Forgetting holidays: A blank holiday list makes NETWORKDAYS.INTL count them as workdays. Double-check that the Named Range expands when you append new rows.
3. Wrong pattern orientation: Remember the first character in Pattern is Monday, not Sunday. A reversed string can swap weekdays and weekends.
4. Ignoring shifts crossing midnight: Simply subtracting times fails when EndTime < StartTime. Always adjust by adding a day to the end timestamp in those cases.
5. Copy-pasting without absolute references: If Start or End references slide when copied, hour totals will be wrong. Lock them with $A$2 or use LET variables.

Alternative Methods

Choosing: Use the simple NETWORKDAYS approach when hours per day are uniform and weekends predictable. Migrate to a shift table when you need sub-daily granularity or complex closures. Move to Power Query once volumes exceed Excel’s comfortable formula limit (roughly 50k intervals).

FAQ

When should I use this approach?

Use it any time you have date-time stamps and need to exclude non-working periods automatically—support SLAs, timesheet validation, project tracking, or payroll.

Can this work across multiple sheets?

Yes. Reference Start and End cells on Sheet 1, holiday list on Sheet 2, and place the formula on Sheet 3. Structured references like ShiftTable[Hours] also work inter-sheet.

What are the limitations?

NETWORKDAYS.INTL only differentiates whole days, not half-days or unique hours. For lunch breaks or split shifts, switch to the SUMPRODUCT shift-table method or VBA.

How do I handle errors?

Wrap formulas in IFERROR to catch missing dates. Use data validation to ensure End ≥ Start. Conditional formatting can highlight negative totals instantly.

Does this work in older Excel versions?

NETWORKDAYS.INTL is available from Excel 2010 onward. Without it (Excel 2007), build a custom weekend mask with WEEKDAY or upgrade. LET and dynamic arrays require Office 365 or Excel 2021, but you can expand them manually.

What about performance with large datasets?

For thousands of rows, array formulas calculate once per interval, so they scale linearly. Shift-tables can blow up if unfiltered—limit rows with FILTER or move heavy lifting to Power Query or the Data Model.

Conclusion

Knowing how to calculate work hours between dates on a custom schedule unlocks precise payroll, accurate invoicing, and reliable project metrics. You learned a quick NETWORKDAYS.INTL-based formula for fixed shifts, a table-driven approach for complex timetables, and tips to keep everything fast and error-free. Master this skill, and you’ll handle anything from simple timesheets to global SLA measurement with confidence. Next, practice on your own data, refine the holiday list technique, and explore Power Query for deep automation. Happy calculating!