Have you tried using the Evaluate Formula procedure to pinpoint the problem? Can you provide an example of the date/time in A2 and B2, along with the expected result and the incorrect result that your formula is returning ?
Hi Experts
Need your help to create a tracker to check the status of task completion. Please check below heads need to consider.
A2 :Receive date& time
B2: Completion date & time
Weekdays : Mon -Fri
Working hours: 9.30 (17.00-2.30)
Holidays
Formula should exclude weekends(Sat&SUN) and non working hours
based on the above details need to consider to fine the completion in days, hour,mintue manner.
When i tried in web and created a formula it's not showing result correctly
=INT(((B2-A2)*24-17+2.5)/24+NETWORKDAYS.INTL(A2,B2,1)-INT(B2-A2))&" Days,"&HOUR(((B2-A2)*24-17+2.5)/24+NETWORKDAYS.INTL(A2,B2,1)-INT(B2-A2))&" hrs, "&MINUTE(((B2-A2)*24-17+2.5)/24+NETWORKDAYS.INTL(A2,B2,1)-INT(B2-A2))&" min and "&SECOND(((B2-A2)*24-17+2.5)/24+NETWORKDAYS.INTL(A2,B2,1)-INT(B2-A2))&"secs"
Thanks in Advance
Srinath
Have you tried using the Evaluate Formula procedure to pinpoint the problem? Can you provide an example of the date/time in A2 and B2, along with the expected result and the incorrect result that your formula is returning ?
Hi,
Try the following formula in C2:
=NETWORKDAYS(A2,B2,$H$2:$H$5)*(9.5/24)+NETWORKDAYS(B2,B2,$H$2:$H$5)*(IF(MOD(B2,1)>=17/24,MOD(B2,1)-17/24,IF(MOD(B2,1)<2.5/24,MOD(B2,1),0))-(9.5/24))+NETWORKDAYS(A2,A2,$H$2:$H$5)*(IF(MOD(A2,1)>=17/24,1-MOD(A2,1)+2.5/24,IF(MOD(A2,1)<2.5/24,2.5/24-MOD(A2,1),0))-(9.5/24))
This is considering that you have - Start Time in A2, End Time in B2, List of holidays excluding weekends are on H2:H6
Bookmarks