Hi Roger & scd
Roger's suggested formula will only work correctly when the delivery
date is the next working day after the received date as in the example
quoted - the 9at the end needs to be 9 hours, i.e.
=(TIME(17,0,0)-MOD(A1,1))+(MOD(B1,1)-TIME(8,0,0))+(NETWORKDAYS(A1,B1,holidays)-2)*TIME(9,0,0)
however you can simplify this to
=MOD(B1,1)-MOD(A1,1)+(NETWORKDAYS(A1,B1,holidays)-1)*"09:00"
or to return a blank if either A1 or B1 are blank
=IF(COUNTBLANK(A1:B1)=0,MOD(B1,1)-MOD(A1,1)+(NETWORKDAYS(A1,B1,holidays)-1)*"09:00","")
Finally, is it possible that the delivery date could be outside work
hours, e.g. on a Saturday, what result would be required then?
--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile:
http://www.excelforum.com/member.php...o&userid=30486
View this thread:
http://www.excelforum.com/showthread...hreadid=502538