# Calculate difference in date/time excluding non-working hours and Thurs/Fri weekends

1. ## Calculate difference in date/time excluding non-working hours and Thurs/Fri weekends

Hello! I am trying to create a formula to measure the time in hours/mins) between 2 dates/times, excluding weekends and only counting between 9am - 5pm on a workday.

=(NETWORKDAYS(C2,D2,Holiday_List)-1)*(Day_End-Day_Start)+IF(NETWORKDAYS(D2,D2,Holiday_List),MEDIAN(MOD(D2,1),Day_End,Day_Start),Day_End)-MEDIAN(NETWORKDAYS(C2,C2,Holiday_List)*MOD(C2,1),Day_End,Day_Start)
Where:
C2 = Start/Create Date/Time
D2 = End/Assigned Date/Time

This formula works perfectly assuming that Saturday and Sunday are the only weekend days. however in other parts of the world the weekend can be Friday/Saturday or Thursday/Friday so I need a formula that can account for this.

I have tried this one but it does not work:
=SUMPRODUCT(INT((WEEKDAY(A2-F\$2:F\$8)+INT(B2)-INT(A2))/7),H\$2:H\$8-G\$2:G\$8)-MOD(A2,1)+MOD(B2,1)-LOOKUP(WEEKDAY(B2),F\$2:H\$8)+LOOKUP(WEEKDAY(A2),F\$2:G\$8)
where:
A2 = start date / time
B2 = end date / time
C2 = difference
F2..F8 = weekday 1 Sun - 7 Sat
G3..G8 = start times for Mon-Sat
H3..H8 = end times for Mon-Sat

How can I measure the difference between dates and times so that a work day is from 9am - 5pm and the weekend can be changed based on world region?

I hope that you can help!!

2. ## Re: Calculate difference in date/time excluding non-working hours and Thurs/Fri weeke

Is the "weekend" always two consecutive days?

Maybe add an offset to each of the dates you work with so that they become skewed across the Western world's idea of a weekend.

3. ## Re: Calculate difference in date/time excluding non-working hours and Thurs/Fri weeke

As ffffloyd says, you could use an "OFFSET" if the weekend is always 2 consecutive days, e.g. for Friday/Saturday weekend

=(NETWORKDAYS(C2+1,D2+1,INDEX(Holiday_List+1,0))-1)*(Day_End-Day_Start)+IF(NETWORKDAYS(D2+1,D2+1,INDEX(Holiday_List+1,0)),MEDIAN(MOD(D2,1),Day_End,Day_Start),Day_End)-MEDIAN(NETWORKDAYS(C2+1,C2+1,INDEX(Holiday_List+1,0))*MOD(C2,1),Day_End,Day_Start)

or for Excel 2010 only you can use NETWORKDAYS.INTL function like this

=(NETWORKDAYS.INTL(C2,D2,7,Holiday_List)-1)*(Day_End-Day_Start)+IF(NETWORKDAYS.INTL(D2,D2,7,Holiday_List),MEDIAN(MOD(D2,1),Day_End,Day_Start),Day_End)-MEDIAN(NETWORKDAYS.INTL(C2,C2,7,Holiday_List)*MOD(C2,1),Day_End,Day_Start)

In the latter 7 denotes a Fri/Sat weekend, change to 6 for Thu/Fri, 5 for Wed/Thu etc. In the first formula +1s would change to +2 for Thu/Fri etc.

4. ## Re: Calculate difference in date/time excluding non-working hours and Thurs/Fri weeke

I was just going through the posts of interest after a very long time..and this was something I was wanting to do...as well..
Calculate the Time Invested for Each Client for a Chartered Accountant...

Is it ok If I may ask of a few things in this thread as its already been solved...?

Regards
e4excel

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1