Calculate working hours between two date/time fields

**teafan** · 09-04-2014, 04:48 AM

Hi,

I am trying to come up with a formula or custom formula that can perform the following:

Calculate the difference between a Start Date (Date/Time) and an End Date (Date/Time) but exclude the hours when our business is NOT open. Our opening hours are 08:00 to 18:00 Monday to Friday and 08:00 to 12:00 on Saturday.

I can do this calculation for Monday to Friday but the Saturday element is tripping me up! I have used the following formula - if anyone can think of a tweak to this to cover the brief that would be ideal as I'd like to share this solution with another business lead and he is reticent to dip into VBA

=(NETWORKDAYS(StartDT,EndDT,HolidayList)-1)*(DayEnd-DayStart)+MOD(EndDT,1)-MOD(StartDT,1)

**daddylonglegs** · 09-09-2014, 07:33 AM

Which version of Excel are you using?

The formula you quoted works if StartDT and EndDt are always within the working hours, will that always be the case?

If so then using Excel 2010 or later you can use this version

=(SUM(NETWORKDAYS.INTL(A2,B2,{1,"1111101"})*{10,4})-IF(WEEKDAY(B2)=7,4,10))/24+MOD(B2,1)-MOD(A2,1)

where A2 contains start date/time and B2 contains end date/time

Calculate working hours between two date/time fields

LinkBack

Thread Tools

Rate This Thread

Display

Calculate working hours between two date/time fields

Re: Calculate working hours between two date/time fields

Thread Information

Users Browsing this Thread

Similar Threads

Calculate start date & time of task (working hours)

[SOLVED] Calculate time for working hours when start time falls outside of working hours

How to calculate difference between two date & time along with working hours and days

How to calculate difference between two date & time along with working hours and days

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

Bookmarks

Bookmarks

Posting Permissions