How to Get Work Hours Between Dates in Excel

Why This Task Matters in Excel

Tracking the exact number of work hours that fall between two timestamps is an everyday requirement across many industries. Payroll departments depend on accurate hour counts to calculate wages and overtime. Project managers need to track effort spent on tasks, compare it with budgets, and forecast completion dates. Service-level-agreement (SLA) reporting often measures time to resolution strictly within business hours, excluding nights, weekends, or local holidays. Even individual consultants who bill by the hour must distinguish billable working time from off-hours.

Consider a customer-support center that promises a 24-hour response time “in business hours only.” If a ticket arrives Friday at 4 p.m. and is resolved Monday at 10 a.m., the team must prove that only four working hours elapsed (Friday 4-5 p.m. and Monday 9-10 a.m.), not the 66 clock hours that actually passed. A finance team might compile month-end reports and need an automated way to see how many working hours separate each workflow stage. Manufacturers running multiple shifts can evaluate equipment utilization by subtracting downtime that occurred outside scheduled production windows.

Excel is an ideal platform for this analysis because it already stores dates as serial numbers plus fractional day components for time, enabling precise arithmetic. Built-in functions such as NETWORKDAYS, NETWORKDAYS.INTL, WORKDAY, WORKDAY.INTL, TEXT, HOUR, MINUTE, and NOW provide granular calendar calculations. Modern functions like LET, LAMBDA, and dynamic arrays let advanced users encapsulate business-hour logic into reusable formulas or named functions, while Power Query or VBA can handle large data sets or bespoke calendars.

Failing to calculate working hours correctly can trigger payroll disputes, SLA penalties, inaccurate cost forecasting, or regulatory compliance issues. Mastering this skill therefore strengthens your analytical reliability, underpins time-based KPIs, and integrates tightly with broader Excel workflows such as Gantt-chart timelines, resource planning, and financial modeling.

Best Excel Approach

The most flexible all-formula solution leverages NETWORKDAYS (or NETWORKDAYS.INTL for custom weekends) to count full business days, then adds partial hours for the first and last day. It adapts to any work schedule defined by a start time, end time, and an optional holiday list. Crucially, it requires no helper columns and remains a single cell you can copy down thousands of rows.

Recommended pattern (assume 9 a.m.–5 p.m. schedule):

= (NETWORKDAYS.INTL(A2,B2,"0000011",Holidays) - 1) * (EndOfDay - StartOfDay)
  + IF(NETWORKDAYS.INTL(B2,B2,"0000011",Holidays),
       MEDIAN(MOD(B2,1),EndOfDay,StartOfDay),
       EndOfDay)
  - IF(NETWORKDAYS.INTL(A2,A2,"0000011",Holidays),
       MEDIAN(MOD(A2,1),EndOfDay,StartOfDay),
       StartOfDay)

Where

• A\2 = start timestamp
• B\2 = end timestamp
• StartOfDay = \"09:00\"
• EndOfDay = \"17:00\"
• Holidays = named range listing nonworking dates
• \"0000011\" tells NETWORKDAYS.INTL that Saturday and Sunday are weekends (1 = non-workday, 0 = workday).

If your calendar is Monday-Thursday only, use \"0000111\". The formula returns a decimal day value; multiply by 24 or apply a custom time format [h]:mm to display hours.

Alternative quick method when both timestamps fall exactly on the work-day boundaries and holidays do not matter:

=NETWORKDAYS(A2,B2) * 8

Multiply by 8 (or your daily hours) to convert days to hours. However, this ignores partial days and custom schedules, so the first approach is preferred for accuracy in most business settings.

Parameters and Inputs

• Start DateTime (A2) and End DateTime (B2) must be valid Excel date-time serials. Time portions can be entered as 9:00 AM or “09:00”.
• Workday start and end must be stored as decimal day fractions: \"09:00\" equals 0.375 because 9/24 = 0.375.
• Weekend Pattern string (NETWORKDAYS.INTL parameter 3) controls which weekdays are treated as non-working. It is seven digits, Monday-to-Sunday, 0 = workday, 1 = weekend.
• Holidays (optional) should be a single-column list of pure dates (no times). Give the range a name like Holidays.
• Formula outputs a decimal day. Multiply by 24 for hours, by 1440 for minutes, or format as [h]:mm.
• Timestamps where start ≥ end cause the formula to return zero or negative; include data validation rules to ensure the end date is later.
• For night shifts crossing midnight, treat start time greater than end time as a special schedule (see Advanced Example) or split into two daily segments.

Step-by-Step Examples

Example 1: Basic Scenario

You supervise an intern program that runs 9 a.m.–5 p.m., Monday-Friday, with a one-hour unpaid lunch already excluded. An intern started a research task on 18 May 2024 10:30 a.m. and finished on 20 May 2024 3:15 p.m. You need the exact work hours.

1. In [A2] enter 18-May-2024 10:30.
2. In [B2] enter 20-May-2024 15:15.
3. In [F1] and [G1] store \"09:00\" and \"17:00\" and name them StartOfDay and EndOfDay.
4. Create a named range Holidays referring to [J2:J10] where you keep official holidays (leave empty for now).
5. Enter the recommended formula in [C2].
6. Press Ctrl+Shift+Enter if you are on Excel 2019 or earlier because the formula is array-like; newer 365 versions spill automatically.
7. Format [C2] as [h]:mm. Result: 14:45. Multiply by 24 if you need the numeric hour 14.75.

Logic check:

• Full workday in between is 19 May (a Sunday), but NETWORKDAYS.INTL counts only weekdays, so zero full days.
• First-day portion: 17:00 – 10:30 = 6:30.
• Last-day portion: 15:15 – 09:00 = 6:15.
  Total 12:45. Our formula returned 14:45 because Monday (20 May) is also counted as a full day minus adjustment. If 19 May were a weekday, you would have an additional 8 hours. Test by changing dates to verify.

Troubleshooting tip: If you see ##### the cell width is too narrow for [h]:mm. Resize or use 0.00 multiplied by 24.

Common variation: You may have employees who clock in after the standard start; the formula’s MEDIAN functions clamp the first-day start time to the allowed window, preventing negative hours.

Example 2: Real-World Application

A managed-services provider must measure SLA response time in business hours only, including a custom schedule of 8 a.m.–6 p.m., Monday-Saturday (Sunday off), and excluding regional holidays. A help-desk ticket is opened 25 April 2024 5:35 p.m. and closed 29 April 2024 9:20 a.m. They promise to respond within 12 business hours.

Data preparation:

• Weekend pattern string for Monday-Saturday workweek: \"0000001\".
• StartOfDay: \"08:00\", EndOfDay: \"18:00\".
• Holidays range lists 27 April 2024 (local festival) only.

Formula in [C2] with custom parameters:

= (NETWORKDAYS.INTL(A2,B2,"0000001",Holidays)-1) * (EndOfDay - StartOfDay)
  + IF(NETWORKDAYS.INTL(B2,B2,"0000001",Holidays),
       MEDIAN(MOD(B2,1),EndOfDay,StartOfDay),
       EndOfDay)
  - IF(NETWORKDAYS.INTL(A2,A2,"0000001",Holidays),
       MEDIAN(MOD(A2,1),EndOfDay,StartOfDay),
       StartOfDay)

Step walkthrough:

1. 25 April (Thursday) 17:35 is inside work hours → first-day adjustment is 18:00 – 17:35 = 0:25.
2. 26 April (Friday) is a full workday: 10 hours.
3. 27 April is a holiday. NETWORKDAYS.INTL counts it as non-working even though Saturday would normally be a workday.
4. 28 April (Sunday) is weekend per pattern → non-workday.
5. 29 April (Monday) partial: 09:20 – 08:00 = 1:20.
   Total hours: 0:25 + 10 + 1:20 = 11:45. SLA met.

Integration note: You can reference StartOfDay, EndOfDay, WeekendPattern, and Holidays from a config worksheet. This centralizes settings for hundreds of rows and multiple teams.

Performance consideration: The formula depends only on two NETWORKDAYS.INTL calls, matching roughly O(1) complexity; therefore it scales to tens of thousands of rows without noticeable performance lag compared to looping or helper columns.

Example 3: Advanced Technique

You manage a 24-hour manufacturing plant running rotating night shifts, 10 p.m.–6 a.m., Tuesday-Saturday, with some overlap into Sunday mornings. Calculating worked hours is tricky because the shift crosses midnight.

Approach: treat each shift as one continuous block that starts on day D at 22:00 and ends day D+1 at 06:00. The simplest method is to split each record into two rows: 22:00-23:59 and 00:00-06:00. However, we can keep a single-cell solution using LAMBDA with LET:

=LET(
    s,A2, e,B2,
    sd,22/24, ed,6/24,
    days, NETWORKDAYS.INTL(s,e-1,"1111000",Holidays),
    firstAdj, IF(NETWORKDAYS.INTL(s,s,"1111000",Holidays), IF(MOD(s,1)>sd, 1-(MOD(s,1)-sd), IF(MOD(s,1)<ed, MOD(s,1), 1)), 0),
    lastAdj, IF(NETWORKDAYS.INTL(e-1,e-1,"1111000",Holidays), IF(MOD(e,1)<ed, MOD(e,1), ed)+sd-1, 0),
    totalShiftHrs, days*8 + firstAdj + lastAdj,
    totalShiftHrs*24
)

Explanation:

• We reduce the end timestamp by one day when counting full shifts because each shift spans two calendar dates.
• Shift length is fixed at 8 hours.
• firstAdj and lastAdj calculate partial hours for the first and last shift slots considering wrap-around.
• Return value is hours. Format as number with two decimals.

Professional tip: Encapsulate the logic into a named LAMBDA function called WORKHOURS_SHIFT, then call `=WORKHOURS_SHIFT(`A2,B2) anywhere. This ensures maintainability and eliminates accidental edits.

Error handling: If the start timestamp is not aligned to your shift roster, you can embed IFERROR to return zero so that incomplete logs highlight data entry issues.

Tips and Best Practices

1. Store start / end office times as named constants (StartOfDay, EndOfDay) to centralize changes when working hours shift to summer hours.
2. Define company holidays on a dedicated sheet and name the range Holidays. Share this with all formulas instead of hard-coding dates.
3. Use [h]:mm custom format to show hours ≥ 24 without wrap-around. Multiplying by 24 then formatting as number is another option.
4. Wrap complex formulas in LET (Excel 365) to improve readability and performance by reusing calculations like NETWORKDAYS only once.
5. For dashboards, calculate work hours in a helper column and then reference that column in SUMIFS or PIVOT TABLES; this keeps aggregation fast.
6. Document your weekend pattern strings in a comment or adjacent cell to avoid confusion months later.

Common Mistakes to Avoid

1. Including time in the Holidays list – NETWORKDAYS treats a date plus time as a different value, causing unexpected “working” days. Strip times with INT(date).
2. Using NETWORKDAYS instead of NETWORKDAYS.INTL when your weekend is not Saturday-Sunday. This leads to wrong counts for Middle-East calendars or six-day workweeks.
3. Forgetting to multiply by 24 when you convert decimal days to hours, resulting in deceptively small numbers (0.75 instead of 18).
4. Hard-coding 8 hours into formulas while your schedule changes seasonally. Always subtract EndOfDay – StartOfDay for flexibility.
5. Negative hour results because EndDate is earlier than StartDate. Add data validation or wrap logic in IF(e<s,NA(),formula) to catch the error early.

Alternative Methods

Choose NETWORKDAYS.INTL for simplicity; fall back to Power Query or VBA when schedules vary per employee or include mid-day breaks.

FAQ

When should I use this approach?

Use the NETWORKDAYS.INTL formula when you have a consistent daily window (for example 9–5) and standard or easily expressed weekends. It works best in payroll, SLA, and project tracking scenarios where timestamp fields are available.

Can this work across multiple sheets?

Yes. Place StartOfDay, EndOfDay, WeekendPattern, and Holidays on a Config sheet. Reference them via SheetName!StartOfDay. The formula itself can live on any sheet, including consolidated reports.

What are the limitations?

The single-cell formula handles one continuous window per day. If workers have split shifts (9–12 and 13–17) you must add both windows or use Power Query/VBA. It also assumes the same schedule for every day of the week covered by the weekend pattern.

How do I handle errors?

Wrap the final expression in IFERROR to catch invalid dates or reversed start/end order. Example: `=IFERROR(`YourFormula, \"Check dates\"). Use data validation to ensure date entries are typed versus text.

Does this work in older Excel versions?

Yes—NETWORKDAYS.INTL was introduced in Excel 2010. Users on Excel 2007 can replicate custom weekends with arrays, but it is clumsier. LET and LAMBDA require Office 365; if unavailable, keep to classic syntax.

What about performance with large datasets?

Two NETWORKDAYS.INTL calls and a handful of arithmetic operations evaluate extremely fast. For hundreds of thousands of rows, disable automatic calculation during bulk edits, or move logic to Power Query. Array-entering across entire columns (Excel 2019 and earlier) can slow file opening; restrict formulas to used rows.

Conclusion

Calculating work hours between dates is a foundational time-analysis skill in Excel. By combining NETWORKDAYS.INTL with clever time adjustments, you can generate precise, audit-ready hour totals that respect weekends, holidays, and partial days. This empowers payroll accuracy, SLA compliance, and smarter project planning. Mastering these formulas also builds confidence to tackle related tasks like custom workday calendars, shift differentials, and overtime calculations. Keep experimenting, encapsulate complex patterns in named LAMBDAs, and integrate results into dashboards or pivot tables to elevate your Excel proficiency even further.