1. ## Please chcek the formula - night shift tracker

Hi Experts

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"

Srinath

2. 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 ?

3. 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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•