Thanks, both! I realized the same-day problem a few days ago and had
intended to check back in for clarification. Looks like you beat me to it.

Your "if" statement was an even bigger help, as much of our documentation
predates the use of time stamps, and just relies on dates.

And as for your question about what happens during off-times, we basically
told Mgmt that anything we don't get between 8-5 M-F is just stamped as of
8AM the next workday.

Again, thanks to both of you!

"daddylonglegs" wrote:


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