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

1. ## 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:

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.

2. ## Re: Formula to calculate working hours between two dates not working correctly?

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!

Thanks

3. ## Re: Formula to calculate working hours between two dates not working correctly?

Pl attach a dummy file.

4. ## Re: Formula to calculate working hours between two dates not working correctly?

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

5. ## Re: Formula to calculate working hours between two dates not working correctly?

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

6. ## Re: Formula to calculate working hours between two dates not working correctly?

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?

7. ## Re: Formula to calculate working hours between two dates not working correctly?

I have changed formula in D column .Pl see

9. ## Re: Formula to calculate working hours between two dates not working correctly?

Originally Posted by kvsrinivasamurthy
I have changed formula in D column .Pl see
Fantastic, great job.

Thanks so much!

10. ## Re: Formula to calculate working hours between two dates not working correctly?

If U2 is the opening time and T2 the closing time then you have those two the wrong way round in your formula (and E2 and F2 on the attachment). That won't be a problem when both dates are the same but over longer periods you will get incorrect results, so formula in attachment should be as follows:

That will count all the hours between 08:30 and 17:00 on your attachment........but as you say it doesn't work if the dates/times are outside work hours. Assuming that only the received time can be outside work hours you can change to this version to calculate correctly in all cases:

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1