Please chcek the formula - night shift tracker

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