Calculate period of time, excluding non-working hours

[Grace79]

Hello

I'm not sure if this is possible but any guidance or advice would be gratefully received!

I would like to be able to calculate the time elapsed between different times, where more often than not, these will fall on different days. However, I also would like to be able to:

• exclude non working hours
• be able to change the working/non working hours

Any help and/or top tips welcomed!

TIA
Grace

[Bob Phillips]

Do you want to give some specifics, the info is a bit sparse.

[Grace79]

Sorry about that - here is an example of what I am trying to calculate:

Opening times
Monday 9:00:00 AM - 5:00:00 PM
Tuesday 9:00:00 AM - 5:00:00 PM
Wednesday 10:00:00 AM - 5:00:00 PM
Thursday 9 :00:00 AM - 5:00:00 PM
Friday 9:00:00 AM - 5:00:00 PM
Saturday 9:00:00 AM - 12:00:00 PM

Call logged (date and time) 15/12/2006 16:00
Call closed (date and time) 18/12/2006 10:00

I would like to know the time elapsed between the call logged and call closed, but only counting time when the branch is open. For example, the time elapsed for the example given is 5 hours.

However, I would like to be able to change the opening hours of the branch if possible too.

Thanks
Grace

[Grace79]

I've found a formula which should work - but doesn't!
(I have no affiliation with the site)

http://www.cpearson.com/excel/DateTimeWS.htm#WorkHours

The only thing I can think of is that the American/English difference in displaying the date is making a difference?

[BilasTasol]

Hi Grace:

As Chip mentions, the Analysis ToolPak is required for his function to work. If you haven't installed this, select Tools/Add-Ins... and tick the Analysis ToolPak checkbox. If the function still doesn't work for you, maybe the number of different hours of your working days is causing a problem?

Regards,

Tony.

[Grace79]

Hi Tony

Thanks for your reply.

I have got Analysis ToolPak activated so this is not the problem.

For the moment I am putting my working hours in as basic 9-5 each day and not worrying about this secondary issue until I have got this to work. I am getting a #VALUE! so I am assuming this is a basic problem with the actual formula?

[Bob Phillips]

It would be a darn sight easier if you opened at 9 on Wed.

[JimmyTheHand]

For lack of a better solution, try the attached file. I solved (I think) the problem with a custom function. It's called WorkTime and has two parameters: Login and Logout. See the blue cells for test results.

Note that
Private Function DayStart and Private Function DayEnd
both use a fixed range (B2:C8) as reference. If the table with the opening times is moved to other parts of the sheet, the range definition should be modified accordingly.

Jimmy