Please help me with , difference(aging) between two columns ( in days and hours ) . Only wor

[danish Sekhri]

Please help me with , difference(aging) between two columns ( in days and hours ) . Only working days ie Sunday to Thursday and business hours 8AM - 5 PM .

First Column-
29/3/18 14:34:48


Second Column-
9/11/2018 14:30:00


Please Help

 

[Peter Bartholomew]

The basis of a solution is
= NETWORKDAYS.INTL( start, end, 7 )
which gives the working days.
The time difference would be
24 * (MOD(end,1) - MOD(start,1))

Whilst the results would be correct, they might show it in the form
161 days minus 1 hour

To get the hours as a positive number
= MOD( 24 * (MOD(end,1) - MOD(start,1)), 9 ),
in which case you would need to subtract a day
= NETWORKDAYS.INTL( start, end, 7 ) - (MOD(start,1) > MOD(end,1))

 

[danish Sekhri]

Hi Peter

I am not getting the required result by using the above formula.

difference(aging) between two columns ( in days and hours ) . Only working days ie Sunday to Thursday and business hours 8AM - 5 PM .

Start
Sep 9, 2018, 12:16 PM

End
9/10/18 2:30 PM

 

[Peter Bartholomew]

Did you get 161 days, less 0.08 hours?
If so, how would you like the result to be displayed?
If not, what do you think to answer should be?

 

[danish Sekhri]

Hi Peter,

I am not able to upload the file here but if you can share your Email ID i cna share you the file.

When i am trying the formula its not working i mean its giving me #value .

 

[Peter Bartholomew]

Does this help? If not we may need to explore 'conversations' which I take to be for private communications.