asked on

Time difference between dates without weekend

Hi All,

I have a scenario in Incident report.

Start date: 12/26/2016 23:58 (in E2 cell)
Resolved date: 12/27/2016 12:47 (in F2 cell)

Formula I used is :
=IF(OR(E2="",F2=""),"",IF(NETWORKDAYS(E2,F2)-1=0,HOUR(MOD(F2-E2,1))&" Hour "&MINUTE(MOD(F2-E2,1))&" Minutes",NETWORKDAYS(E2,F2)-1&" Days "&HOUR(MOD(F2-E2,1))&" Hour "&MINUTE(MOD(F2-E2,1))&" Minutes"))

Result is coming as 1 Days 12 Hour 49 Minutes

But the actual difference is 12 hours 49 Minutes. So it is calculating 1 extra day since networkdays consider 1 complete day when the date changes.

Please help to get the correct output.

It works correctly if start and resolved dates are same which is also the needed.

Thanks in advance.

Chetan Pawar

Shums Faruk

Hi,

Try below:

=IF(OR(E2="",F2=""),"",NETWORKDAYS(E2,F2)-1-MOD(E2,1)+MOD(F2,1))

Open in new window

See the result:

Subodh Tiwari (Neeraj)

Does this work for you?

=IF(OR(E2="",F2=""),"",IF( NETWORKDAYS(E2,F2)-2 =0,HOUR(MOD(F2-E2,1) )&" Hour "&MINUTE(MOD(F2-E2,1))&" Minutes",NETWORKDAYS(E2,F2 )-2&" Days "&HOUR(MOD(F2-E2,1))&" Hour "&MINUTE(MOD(F2-E2,1))&" Minutes"))

Open in new window

Chetan Pawar

ASKER

Hi Shum : Thanks for reply.

It gives wrong result on cases below:

Start        Resolved        Result        Remark
1/1/2017 0:41      1/4/2017 16:05      63:24:28        Actual difference is 49:24
12/31/2016 12:35      1/2/2017 10:55      ###        Negative value

Chetan Pawar

ASKER

Hi Neeraj,

Thanks for reply.

Again -2 is giving -ve result for some cases.
e.g.

Start date: 1/7/2017 10:34
End date: 1/9/2017 10:31

Subodh Tiwari (Neeraj)

Yes you are right.
Will look at this after a while.

Chetan Pawar

ASKER

Hi Shum : Sorry for first case, actual difference is 63:24 which is correct.

challenge is for -ve outcome .

Thank you.

Prakash Samariya

Can you please change your formula with below ones?

=IF(INT(B2-A2)>0,INT(B2-A2)&" Days ","")&IF(HOUR(B2-A2)>0,HOUR(B2-A2)&" Hours ","")&IF(MINUTE(B2-A2)>0,MINUTE(B2-A2)&" Minutes","")

Open in new window

It will give correct results as you want in your excel Please see snapshot attached here:

One more thing NetworkDays function will works only with Days to differentiate, so date 27-26 will always gives you 1 and do not count hours into it!
Hope it helps you!

Chetan Pawar

ASKER

Hi Prakash,

Thanks. This is giving correct difference but includes weekends. We need to remove weekend.

Even if ticket is created over weekend, we need to consider only weekday hours to calculate the difference.

Thank you,
Chetan Pawar

Subodh Tiwari (Neeraj)

Okay does this work for you?

=IF(OR(E2="",F2=""),"",IF(OR( NETWORKDAYS(E2,F2)-1=1,NETWORKDAYS(E2,F2)=1),HOUR(MOD(F2-E2,1) )&" Hour "&MINUTE(MOD(F2-E2,1))&" Minutes",NETWORKDAYS(E2,F2 )-1&" Days "&HOUR(MOD(F2-E2,1))&" Hour "&MINUTE(MOD(F2-E2,1))&" Minutes"))

Open in new window

Chetan Pawar

ASKER

Excellent!! This is perfectly working. :)

One more help to display 0 days wherever applicable. Plz can you add it and share.

Thanks in advance.

Shums Faruk

Modifying Neeraj's formula for 0 Days:

=IF(OR(E2="",F2=""),"",IF(OR( NETWORKDAYS(E2,F2)-1=1,NETWORKDAYS(E2,F2)=1),"0 Days "&HOUR(MOD(F2-E2,1) )&" Hour "&MINUTE(MOD(F2-E2,1))&" Minutes",NETWORKDAYS(E2,F2 )-1&" Days "&HOUR(MOD(F2-E2,1))&" Hour "&MINUTE(MOD(F2-E2,1))&" Minutes"))

Open in new window

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Shums Faruk

Chetan,

If you are satisfied with Neeraj's code, pleas give him full points.

Chetan Pawar

ASKER

Perfectly provided the solutions!! Thanks Neeraj