I don't recognise any lack of robustness in the formula I posted. It
does exactly what I said it would, which is to give the total business
hours between the two time/dates, based on a 9 hour day (with no meal
break) and assuming the start and end times both fall within those
business hours
That's true, but making assumptions usually gets *me* into trouble!
Can we assume that the scope of this application will always meet your
assumed criteria? Maybe, maybe not.
I admit that I tested your formula outside the assumed criteria, used
non-workdays, but that should be something that the formula accounts for
(IMHO):
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!
Biff
"daddylonglegs"
wrote in message
news:daddylonglegs.26tcba_1145956801.9069@excelfor um-nospam.com...
Hi Biff
I don't recognise any lack of robustness in the formula I posted. It
does exactly what I said it would, which is to give the total business
hours between the two time/dates, based on a 9 hour day (with no meal
break) and assuming the start and end times both fall within those
business hours
I think the meal break problem here clouds the issue, I see what you
have attempted to do with the formula you posted but it means that in
some circumstances a later end time/date can result in a shorter time
period returned, e.g.
A1 = 25 Apr 06 09:00, B1 = 27 Apr 06 16:30 24.5
A1 = 25 Apr 06 09:00, B1 = 28 Apr 06 08:15 24.25
Your formula also gives some strange results in other circumstances
A1 = 25 Apr 06 09:00, B1 = 25 Apr 06 10:00 10
Surely this should be 1 hour not 10?
If start/end times outside business hours ARE to be allowed then, again
assuming a nine hour day, from 08:00 to 17:00 and B1 not less than A1,
I'd suggest this formula.
=(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)
--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile:
http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=535417