I think I managed to fudge it: I made the offset (9 hours) smaller than
the working hours in the day by increasing the EndDT by 1 second (i.e.
17:00:01). That way a full day remains on the same day instead of
rolling over. From a purist point of view it isn't correct, but the
result I'm seeking won't be skewed by 1 second (but will be by rolling
over to the next day). I'll put it to the test on my data and see if
there is anything else the fudging might affect....! Once again, thanks
for helping me out, I realy do appreciate your time in doing so!

wrote:
Hey daddylonglegs, thanks for your response! This is a whole lot better
than how it was before.... I'm chewing on the impact of the minor
problem within the environment I want to use this (SLA calculations) -
it opens up a little can of little worms ;) Is it not possible to fudge
the formula so that it doesn't roll over into the next day...?

Regards,
Chris
daddylonglegs wrote:
Hi Chris

That makes things a little more complicated, but assuming your StartDT
& Time can be any time, evenings weekends, holidays etc. then you can
use this formula

=WORKDAY(A2,(D2-B2+IF(NETWORKDAYS(A2,A2,G$1:G$10),MEDIAN(B2,C2,MOD (A2,1)),C2))/(C2-B2),G$1:G$10)+B2+MOD(D2-B2+IF(NETWORKDAYS(A2,A2,G$1:G$10),MEDIAN(B2,C2,MOD (A2,1)),C2),C2-B2)

assuming the same setup as previously.

One possible minor problem is that the result would never show the
exact end time of the day but the start time of the next, e.g. given
DayStart of 08:00 and DayEnd of 17:00 with your above example StartDT &
Time of Aug-12-2006 15:23 and offset of 09:00 the result would be
Aug-15-2006 08:00


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=569326