Another complex working hours formula

[Hitesh_Banda]

Hi!

I am creating a template which consists the following:

1. Start Date & Time - contains a start date and time
2. End Date & Time - contains a end date and time
3. nwday1 - Named range (contains non-working 1 day OR first weekly off, it accepts a number from 1 to 7)
4. nwday2 - Named range (contains non-working 2 day OR second weekly off, it accepts a number from 1 to 7)
5. Holidays_List - Contains the official list of Company holidays
6. s_time - Named range (contains shift start time)
7. e_time - Named range (contains shift end time)

Requirement-
Need the exact time (HH:MM:SS format) between 1 & 2, excluding 4,5,6 along with the time between 6 and 7

Business Rules-

1. An associate can receive a request at any time of the day, including the before shift time and after shift time.
2. An associate can start and complete working on a request only between his shift hours (between s_time and e_time).
3. A request worked upon by the associate can span multiple days.
4. If a request is received before or after shift hours, the next consecutive working date and time needs to be considered for Start time

Any help would be greatly appreciated.
Thanks.

 

[DRSteele]

I think that what you're after is a way to calculate NetworkHours in a similar way to function NETWORKDAYS. Unfortunataley, no such function yet exists. The alternative algorithm is complex, and I've dealt with it before. Peruse these threads and link and determine whether or not any of this helps: TAT minutes including weekend http://www.mrexcel.com/forum/excel-...s-between-two-dates-excluding-weekends-5.html

Also, please go here, sign up and vote for the creation of this kind of function. If enough votes accumulate, Microsoft might make it a reality. Create NETWORKHOURS as a new function

 

[Hitesh_Banda]

DRSteele,

I'll definitely vote for creating networkhours function. In the meantime, I'll check the threads and let you know which one of them helps.

Thanks.