srinath2015
New member
- Joined
- Aug 5, 2015
- Messages
- 3
- Reaction score
- 0
- Points
- 0
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
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