Difference in time HOURS/MINS betwn 2 dates excluding Holidays,Leaves,WkOffs+Overtime

Dear Forum,

I am picking a cue from the recent post on Time DIfference Between 2 Dates..

http://www.excelforum.com/excel-work...-weekends.html

Difference in time HOURS/MINS betwn 2 dates excluding Lunch Tea Breaks, Holidays,Leaves,WkOffs but including Overtime

It was a sheer coincidence that I was looking for something similar for calculating the time in Hours or Mins (Billable Hours) for people involved in data entry work.

Just some things additional are as follows:

Lunch & Tea Breaks and Overtime over and above the the regular shift-timing..

The Start Date, Closing Date and Start Time and Closing Time as well as Overtime Rates is a new addition...

Regards
e4excel

It's not clear to me if the lists in J & L will only ever contain dates that appear within the time frame specified in C2:C3

What adds to the confusion is you state "weekly offs" as being Sun, Sat & Tue (D2:D4) yet list Dates of those weekdays in the Overtime Dates, eg: Tue 23-Nov-10 has overtime assigned yet supposedly Tues is not a working day.

Ignoring the weekly offs and assuming that J & L will not always contain dates within the specified window:

=SUMPRODUCT(ISNA(MATCH(ROW(INDIRECT($C$2&":"&$C$3)),$J$2:$J$6,0))+0,LOOKUP(WEEKDAY(ROW(INDIRECT($C$2&":"&$C$3))),$E$2:$E$8,$H$2:$H$8-$G$2:$G$8-$I$2:$I$8))+SUMIFS($N$2:$N$7,$L$2:$L$7,">="&$C$2,$L$2:$L$7,"<="&$C$3)

the above format as [hh]:mm would return 230:30 for the data provided but again to repeat this does not account for "Weekly Offs" given the aforementioned ambiguity.

In effect we need to know whether or not a date listed as having overtime overrides the Weekly Off constraint or vice-versa.

You still need to clarify...

A quick way to provide clarity is to provide an expected answer for the sample data
calculating the result(s) manually prior to posting the question to the board would also flag up any inconsistencies in your sample dataset

So, of the three days listed as Off: Sun,Sat,Tue which are valid ?

Further to the above - should there be any OT entries listed for "Off Days" ?
(you have entries for Tue 23-Nov, Sun 5-Dec, Tue 7-Dec)

If it's possible that there can be OT on weekdays otherwise listed as "Off Days" does this mean that the OT should be ignored or that the "Off Day" should be ignored for that specific date ?

In terms of mass calculations - well yes - this formula is both inefficient and Volatile so applying en masse is not great.

Daddlylonglegs might be able to conjure up a lightweight alternative for you.

Alternatively you might consider using a UDF which though unlikely to calculate faster in itself -- won't need to be Volatile and thus will only calculate when necessary (precedent cells being altered)