Rquired -Excel tracker for business days &hours (night shift)

[call2shree]

Hi Experts


Required your support to include only week days and working hours on this below mentioned formula.

IF(INT(B9-A9)>0, INT(B9-A9) & " days, ","") & IF(HOUR(B9-A9)>0, HOUR(B9-A9) & " hours, ","") & IF(MINUTE(B9-A9)>0, MINUTE(B9-A9) & " minutes and ","") & IF(SECOND(B9-A9)>0, SECOND(B9-A9) & " seconds","")


A9 :Receive date& time

B9: Completion date & time

Weekdays : Mon -Fri

Working hours: 9:30 minutes (17:00-2:30)
example: 12/8/2015 17:00 to 13/8/2015 02:30 am as 1 day

Holidays

Formula should exclude weekends(Sat&SUN) and apart from working hours

based on the above details need to consider to fine the completion in days, hour,mintue manner.



Thanks in Advance

Srinath

 

[Deepak]

Hi,

It's bit ugly...

=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"

 

[call2shree]

Hi Experts


Required your support to include only week days and working hours on this below mentioned formula.

IF(INT(B9-A9)>0, INT(B9-A9) & " days, ","") & IF(HOUR(B9-A9)>0, HOUR(B9-A9) & " hours, ","") & IF(MINUTE(B9-A9)>0, MINUTE(B9-A9) & " minutes and ","") & IF(SECOND(B9-A9)>0, SECOND(B9-A9) & " seconds","")
View attachment 21610

A9 :Receive date& time

B9: Completion date & time

Weekdays : Mon -Fri

Working hours: 9:30 minutes (17:00-2:30)
example: 12/8/2015 17:00 to 13/8/2015 02:30 am as 1 day

Holidays

Formula should exclude weekends(Sat&SUN) and apart from working hours

based on the above details need to consider to fine the completion in days, hour,mintue manner.



Thanks in Advance

Srinath

Hi,

It's bit ugly...

=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 for your reply Deepak

It's calculating ..but not as per my requirement.
Need to calculate difference between the start time & end time. And consider only Business days (exclude sat ,sun) & Business Hours (evening 5 p.m to morning 2.30 am)night shift .

When I try your formula attached result appeared. Please advise.

Thanks in Advance
Srinath

 

[Deepak]

My bad...

Will check !!

Pls upload a sample file with some around 10 manual calculation..