=(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)
Start Date (Request Date) | End Date | Desired Result | remarks |
2/14/2018 18:30 | 2/15/2018 18:45 | 45 mins | counting of minutes will start 8am(working hours) and will exclude the 30mins(after 6pm) |
2/15/2018 18:00:00 | 2/18/2018 8:30 AM | 30 mins | will only include the mins within working hrs and working days (mon-fri) |
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?