# Thread: Calculate only working hours between two dates excluding weekends Thanks:  2 Post #3407941 (1)Post #2105796 (1) Likes: 0

1. ## Calculate only working hours between two dates excluding weekends

How should I calculate working hours between two dates? Say if start at 9/25/2009 7:26:13 PM and finish at 10/20/2009 9:46:13 AM, the function should return 245:20:00 because the working hours are from 8am to 11 PM (8 - 23), and there are weekends between the dates. Preferably the function should work like the NETWORKDAYS() function, but it should also include the time, not just the dates. And also how can I exclude holidays if there are any.  Reply With Quote

2. ## Re: Calculate only working hours between two dates excluding weekends

If start date and time is in A1, start date and time in A2 . . .
Code:
`=((NETWORKDAYS(A1,A2)-2)*15/24)+23/24-MOD(A1,1)+MOD(A2,1)-8/24`
returns 245:20:00 for your given dates/times.
Format the cell containing the formula as [h]:mm:ss  Reply With Quote

3. ## Re: Calculate only working hours between two dates excluding weekends

By the way, my suggestion does not account for holidays. But the NETWORKDAYS function can be made to account for them.  Reply With Quote

4. ## Re: Calculate only working hours between two dates excluding weekends

You could simplify that a little Gerald, i.e.

=(NETWORKDAYS(A1,A2)-1)*15/24+MOD(A2,1)-MOD(A1,1)

Note: this only works assuming start and end time/dates will always be within working hours, if not you'll need a more complex formula....  Reply With Quote

5. ## Re: Calculate only working hours between two dates excluding weekends Originally Posted by barry houdini You could simplify that a little Gerald, i.e.

=(NETWORKDAYS(A1,A2)-1)*15/24+MOD(A2,1)-MOD(A1,1)
Yes you're right. I did think of that, but decided it would be better to show the formula in that longer format to help explain what it does.

Note: this only works assuming start and end time/dates will always be within working hours, if not you'll need a more complex formula....
LOL you're right   Reply With Quote

6. ## Re: Calculate only working hours between two dates excluding weekends

Gerald,

If the start time and end time are 8 Am and 6:30 PM, then wht changes should I make in the formula? Also, please let me know how I can exclude the holiday list too.  Reply With Quote

7. ## Re: Calculate only working hours between two dates excluding weekends

Here's a generic formula

=(NETWORKDAYS(A2,B2,holidays)-1)*(J\$3-J\$2)+MOD(B2,1)-MOD(A2,1)

A2 = start time/date
B2 = end time/date
J2 = MF start time, e.g. 08:00
J3 = MF end time e.g. 18:30
holidays = named range containing holiday dates  Reply With Quote

8. ## Re: Calculate only working hours between two dates excluding weekends

Hey Barry does this formula exclude weekends too?  Reply With Quote

9. ## Re: Calculate only working hours between two dates excluding weekends

Yes, it will count hours between the two "timestamps" but including only the defined period on Mondays to Fridays, but excluding weekends and/or any holidays defined.

As previously stated it's only guaranteed to give correct results if the start/end times/dates are within working hours.

If the start or end could be at weekends or evenings, for instance, then you can still calculate the hours but you need a more complicated version of that formula.......  Reply With Quote

10. ## Re: Calculate only working hours between two dates excluding weekends

I am somehow not able to get it to work
I think something wrong in the formating of my cells

I am trying to use the formula given below
=(NETWORKDAYS(H6,N6)-1)*(\$F\$2-\$E\$2)+MOD(N6,1)-MOD(H6,1)
here H6, N6 are in format of date time (like 3/5/2009 2:11:27 PM)
and F2, E2 are in time format (9:00:00 AM and 6:00:00 PM)

So when i calculate for start date
 3/2/09 7:29

and end date
 3/31/09 18:17

Its giving me wrong result as 7:29 ... (this result column i have formated as h:mm:ss )  Reply With Quote

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•