=(NETWORKDAYS.INTL(A1,B1,1)-1)*(1440/(24/10))+IF(A1-ROUNDDOWN(A1,0)<=B1-ROUNDDOWN(B1,0),((B1-ROUNDDOWN(B1,0)-(A1-ROUNDDOWN(A1,0))))*1440,((B1-ROUNDDOWN(B1,0)-(A1-ROUNDDOWN(A1,0))))*1440)
My first answer doesn't take in to consideration about working hours and networking days... apologies, I have looked through and came up with the following:
Code:=(NETWORKDAYS.INTL(A1,B1,1)-1)*(1440/(24/10))+IF(A1-ROUNDDOWN(A1,0)<=B1-ROUNDDOWN(B1,0),((B1-ROUNDDOWN(B1,0)-(A1-ROUNDDOWN(A1,0))))*1440,((B1-ROUNDDOWN(B1,0)-(A1-ROUNDDOWN(A1,0))))*1440)
Example:
A1 = "01/07/2019 15:13:00"
B1 = "08/07/2019 15:03:00"
Answer will return 2990
Which makes sense as the two dates are 1 week apart less 10minutes - in a working week of 3000 minutes. (600 minutes (10hours) per day).
=(NETWORKDAYS.INTL(A1,B1,1)-1)*(1440/(24/10))+IF(A1-ROUNDDOWN(A1,0)<=B1-ROUNDDOWN(B1,0),((IF(B1-ROUNDDOWN(B1,0)<1/3,1/3,IF(B1-ROUNDDOWN(B1,0)>0.75,0.75,B1-ROUNDDOWN(B1,0)))-(IF(A1-ROUNDDOWN(A1,0)<1/3,1/3,IF(A1-ROUNDDOWN(A1,0)>0.75,0.75,A1-ROUNDDOWN(A1,0))))))*1440,((IF(B1-ROUNDDOWN(B1,0)<1/3,1/3,IF(B1-ROUNDDOWN(B1,0)>0.75,0.75,B1-ROUNDDOWN(B1,0)))-(IF(A1-ROUNDDOWN(A1,0)>0.75,0.75,IF(A1-ROUNDDOWN(A1,0)<1/3,1/3,A1-ROUNDDOWN(A1,0))))))*1440)
=IF(OR(TEXT(B1,"ddd")="Sat",TEXT(B1,"ddd")="Sun"),(NETWORKDAYS.INTL(A1,B1,1))*(1440/(24/10)),(NETWORKDAYS.INTL(A1,B1,1)-1)*(1440/(24/10))+IF(A1-ROUNDDOWN(A1,0)<=B1-ROUNDDOWN(B1,0),((IF(B1-ROUNDDOWN(B1,0)<1/3,1/3,IF(B1-ROUNDDOWN(B1,0)>0.75,0.75,B1-ROUNDDOWN(B1,0)))-(IF(A1-ROUNDDOWN(A1,0)<1/3,1/3,IF(A1-ROUNDDOWN(A1,0)>0.75,0.75,A1-ROUNDDOWN(A1,0))))))*1440,((IF(B1-ROUNDDOWN(B1,0)<1/3,1/3,IF(B1-ROUNDDOWN(B1,0)>0.75,0.75,B1-ROUNDDOWN(B1,0)))-(IF(A1-ROUNDDOWN(A1,0)>0.75,0.75,IF(A1-ROUNDDOWN(A1,0)<1/3,1/3,A1-ROUNDDOWN(A1,0))))))*1440))
Hey,
Thanks for the feedback; I have adjusted the formula now to hopefully account for all cases of the start/end time!
Code:=(NETWORKDAYS.INTL(A1,B1,1)-1)*(1440/(24/10))+IF(A1-ROUNDDOWN(A1,0)<=B1-ROUNDDOWN(B1,0),((IF(B1-ROUNDDOWN(B1,0)<1/3,1/3,IF(B1-ROUNDDOWN(B1,0)>0.75,0.75,B1-ROUNDDOWN(B1,0)))-(IF(A1-ROUNDDOWN(A1,0)<1/3,1/3,IF(A1-ROUNDDOWN(A1,0)>0.75,0.75,A1-ROUNDDOWN(A1,0))))))*1440,((IF(B1-ROUNDDOWN(B1,0)<1/3,1/3,IF(B1-ROUNDDOWN(B1,0)>0.75,0.75,B1-ROUNDDOWN(B1,0)))-(IF(A1-ROUNDDOWN(A1,0)>0.75,0.75,IF(A1-ROUNDDOWN(A1,0)<1/3,1/3,A1-ROUNDDOWN(A1,0))))))*1440)
Hey,
For your first example isn't 0 correct then? Seeing as the start date is after 6pm and the end date is at the start of the next day?
In the other case I'm not sure why you are getting 479, just tried it on my screen and I got 11 minutes. A bit strange?