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:

Please Login or Register  to view this content.

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)

Originally Posted by e4excel

Yes there can be a possibilty as people fill on the OFFS and then take a Compensatory OFF on a day of their choice.

As far as I can tell the above is a fairly fundamental issue in terms of calculating this value accurately - given the simple fact that the hours worked are not recorded on a daily basis (ie for each date within window).

Why ?

Well, using the sample, Sun is Weekly Off and there is OT specified for Sun 5-Dec of 1:00 meaning that in reality 8 hours was worked that day (7 hrs standard + 1 hr OT).

You then say that they can take a Compensatory Day Off on a day of their choice.

Now, the above would not be too bad if

a) you could assume the day in lieu is always taken within the specified window

and

b) all working days were the same in terms of their respective standard hours

If the above were the case you could simply ignore the standard time of the Sunday that was worked and just add the OT (ie continue to base standard hrs calc on OFF Day status of each date)
(you can do this because you assume [a] & [b] that the standard Sunday hours would be worked on another non-OFF day within the window and those standard hours would be the same)

However, in reality you can assume neither a) nor b)

a) must be discounted as possibility because in theory the OT worked on an otherwise "Off Day" could occur on the last day in the Window

b) working hours are not the same for each weekday (Sat being 9:00 to 13:00)
result of this: if you worked Sun and took the following Sat off in "compensation" there is then a discrepancy in the standard hours worked

Does the above make sense ?

I think you will need to adopt a compromise in terms of accuracy given the above limitations (resulting from data available)

edit: the only other possibility would be to record those days taken in lieu of OT worked on Week Off days (ie which date was not worked in respect of having worked on 5-Dec)