January 22nd, 2006, 02:11 PM
posted to microsoft.public.excel.newusers
|
|
Can this even be done? Tracking Date / Time Elapsed
Hi daddylonglegs
Nice catch of my error in not using * Time(9,0,0) at the end (or even
more simply * "09:00")!
Your simplification to just using the subtraction of the MOD of the
times, and using Netwokdays -1 rather than 2 is a masterly stroke.
Well done.
--
Regards
Roger Govier
"daddylonglegs"
wrote in
message
news:daddylonglegs.221dca_1137933301.7018@excelfor um-nospam.com...
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
|