Formula to calculate working hours between two dates not working correctly?

Hi Everyone,

I'm new to this forum and need some help with a formula in my worksheet.

Basically this is to calculate the total working hours between two dates. In many occasions this formula works, however there is a certain instance when it doesn't.

The formula is:

=(NETWORKDAYS([@[Date/Time Received]],[@[Date/Time Resolved]])-1)*($U$2-$T$2)+MOD([@[Date/Time Resolved]],1)-MOD([@[Date/Time Received]],1)

Like I say, 99/100 times this works but it appears that when there is only one day separating the two dates, and the time on the first date is later to that on the second date then it returns a value #########.
So for example 06/03/13 12:00 - 07/03/13 12:00 will correctly display 24 hours 1 minute. But if it were to be 06/03/13 12:00 - 07/03/13 11:59 then it wouldn't.

I was just wondering if anybody could assist me in finding a solution for this as it is essentially that I keep a log of activity.

P.S. If you are wondering what is at U2 and T2, they are the opening and closing times.

Thanks a lot.

Update.

A more accurate description of my problem is that the formula will only fail if the Date/Time received time falls outside of the closing time.
This works perfectly if there is a day that separates it, but if I receive it after the closing time and resolve it the next day inside the opening times it will not work!

Please help!

Thanks

Pl attach a dummy file.

As you can see, very dodgy. Works sometimes and others is doesn't.

Pl see the attached file with formula in "d" column

Thanks for helping!

Unfortunately this formula isn't bringing back the value that I wanted. I want to calculate the total work hours/minutes that go in to completing a job. For example in the first row, the value should be 7:04. Because, although we received the issue at 22:45 the previous night we only began working on it at 8:30. Unfortunately it seems to be when we log an issue outside of work hours and then complete it the following day during work hours.

I want to know if there is any way around this.

Thanks again for the effort, but is there anything else I could do to calculate this?

I have changed formula in D column .Pl see

please see its helpful or not

Originally Posted by kvsrinivasamurthy

I have changed formula in D column .Pl see

Fantastic, great job.

Thanks so much!