Biff Wrote:

Date format = M/D/Y

4/1/2005 10:00..........4/2/2005 14:30 returns 4.5

4/2 is a Saturday so the formula should return 7.

4/1/2006 10:00...........4/2/2006 14:30 returns -4.5

Both dates are weekend dates so the formula should return 0

At some point "robustness" turns into overkill and what we offer
totally
depends on how we interpret the needs of the poster.

I struggle with this!



Hello again Biff,

Thanks for your reply.

My experience of situations where this sort of formula is asked for is
that sometimes the start and end dates are never outside office hours,
e.g. when they are project start and end times, but sometimes they are,
e.g. server downtime type queries.

When I don't know which situation applies my approach is usually to
offer the simpler formula, giving it's limitations, then to suggest the
more complex formula if that is what's needed. Of course for the
examples you give above, the more complex formula is required....

=(NETWORKDAYS(A1,B1,)-1)*9+IF(NETWORKDAYS(B1,B1),MEDIAN(MOD(B1,1)*24,17,
8),17)-IF(NETWORKDAYS(A1,A1),MEDIAN(MOD(A1,1)*24,17,8),8)

This will give the correct results as above and also correct results in
all other situations where A1 is not greater than B1.

I believe the formula you posted will give the correct results in the
examples you give but not in many other cases, e.g.

Date format = M/D/Y

4/1/2005 05:00..........4/2/2005 14:30 returns 12

should return 9

....now, if there needs to be a meal break that might need a further
modification.....:)


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