Calculate Working Hours Between Two Dates in Excel

To calculate working hours between two dates in Excel, count the working days with NETWORKDAYS and multiply by the length of your working day: =NETWORKDAYS(A2,B2)*8. If the start and end also have times, such as a ticket opened at 13:00 on Monday and closed at 11:00 on Wednesday, you need a longer formula that trims each end to office hours. This guide covers both, for anyone measuring response times, project effort or service levels.

Whole days: NETWORKDAYS times hours per day#

When work starts at the beginning of one day and ends at the close of another, count the working days and multiply. NETWORKDAYS counts Monday to Friday between two dates and skips weekends:

=NETWORKDAYS(start_date, end_date, [holidays])

Say an audit runs from Monday 2 March 2026 to Friday 13 March 2026, with a public holiday on Monday 9 March, and a working day is 8 hours:

=NETWORKDAYS(B2,C2,E2)*D2

There are 10 weekdays from 2 to 13 March, minus the holiday, which leaves 9 working days. The result is 9 × 8 = 72 hours.

Two details matter:

• Both ends count. NETWORKDAYS includes the start and end dates, so Monday to Friday of one week is 5 days, not 4.
• Holidays need a range. For several holidays, list them in cells such as H2:H5 and use $H$2:$H$5 as the third argument, so the range stays put when you fill down.

The NETWORKDAYS function guide and the article on NETWORKDAYS and WORKDAY cover the function in more depth.

How Excel stores dates and times#

The exact formula below only makes sense once you know how Excel stores time. A date is a whole number of days, and a time is a fraction of a day:

Two consequences follow. First, MOD(A2,1) strips the date and leaves only the time, because it returns the part after the whole number. Second, any time result is in days, so you multiply by 24 to get hours. The guide to calculating the difference between two times explains this with simple start and end times on one day.

To type a date with a time safely, build it from parts: =DATE(2026,3,2)+TIME(13,0,0). That avoids confusion between day-first and month-first date settings.

Exact working hours with start and end times#

When the start and end include times, use this formula. It assumes the start date-time is in A2, the end in B2, the start of the working day in E1 (9:00) and the end of the working day in E2 (17:00):

=((NETWORKDAYS(A2,B2)-1)*($E$2-$E$1)
  +IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),$E$2,$E$1),$E$2)
  -MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),$E$2,$E$1))*24

You can type it on one line; the line breaks only make it easier to read. Here is what each part does:

1. (NETWORKDAYS(A2,B2)-1)*($E$2-$E$1) counts every working day but one as a full working day of 8 hours.
2. MEDIAN(MOD(B2,1),$E$2,$E$1) takes the end time and clips it into office hours. MEDIAN of three values returns the middle one, so a time before 9:00 becomes 9:00 and a time after 17:00 becomes 17:00. This adds the hours worked on the last day.
3. MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),$E$2,$E$1) clips the start time the same way and subtracts it, removing the part of the first day before work began.
4. The IF and the NETWORKDAYS(A2,A2)* parts handle a start or end that falls on a weekend or holiday.
5. *24 turns the answer from days into hours.

To skip holidays, add the holiday range to every NETWORKDAYS in the formula, for example NETWORKDAYS(A2,B2,$H$2:$H$5).

Worked examples of the exact formula#

With a 9:00 to 17:00 day and no holidays, the formula gives these results:

Check the first row by hand: Monday 13:00 to 17:00 is 4 hours, Tuesday is a full 8, and Wednesday 9:00 to 11:00 is 2. That totals 14. In the second row, Friday gives 2 hours, the weekend is skipped, Monday gives 8 and Tuesday gives 1.5, which is 11.5. In the third row, the Saturday start is treated as Monday 9:00, so the answer is 2.

If you prefer to see hours and minutes, drop the *24 and format the cell with the custom format [h]:mm. The square brackets let the hours go past 24.

Different weekends: NETWORKDAYS.INTL#

NETWORKDAYS assumes Saturday and Sunday are the weekend. If your team works a different pattern, use NETWORKDAYS.INTL, which adds a weekend argument:

=NETWORKDAYS.INTL(A2,B2,7,$H$2:$H$5)*8

The pattern form uses seven digits from Monday to Sunday, where 1 means a day off. You can use NETWORKDAYS.INTL in place of every NETWORKDAYS in the exact formula too. It works in Excel 2010 and later. For the difference between counting days and finding a date, see NETWORKDAYS vs WORKDAY.

Common mistakes with working hours#

Most wrong answers come from one of these:

1. Forgetting to multiply by 24. A result like 0.583 is a fraction of a day; × 24 gives 14 hours.
2. Subtracting one day too few or too many. NETWORKDAYS counts both ends. Don't add or subtract 1 to "fix" it unless you mean to.
3. Dates stored as text. If NETWORKDAYS returns #VALUE!, the dates are probably text. Rebuild them with DATE or convert them.
4. Lunch breaks. The formulas count the whole span from 9:00 to 17:00. If an unpaid hour of lunch should come off, use 7 hours per day in the whole-day version.
5. A holiday range that moves. Without $ signs, the holiday range shifts as you fill down and stops matching your list.

For tracking hours worked on each shift rather than elapsed working time, a timesheet in Excel is the better tool.

Practise it#

Try the exercise on this page: it turns a date range with one holiday into working hours. Then practise NETWORKDAYS and WORKDAY in a live spreadsheet, where every formula you type is checked instantly. For plain calendar days between dates, take the days between dates lesson or read the guide to counting days between two dates.