troubleshooting Question

Calculation of business hours between two datetimes in Excel

Avatar of I_play_with_DNA
I_play_with_DNA asked on
Microsoft Excel
12 Comments1 Solution677 ViewsLast Modified:
I have a custom UDF that I have been using for a couple of years to calculate the number of elapsed business hours between two datetimes in Microsoft Excel.  The source data for the calculation is always a spreadsheet of data, including the datetimes, exported from Microsoft CRM.  

Recently, this UDF stopped functioning properly, but I cannot seem to determine why.  As far as I can tell, there has been no change in the data exported, so the UDF should still work.  Now, it gives the correct answer for some datetime pairs and wildly incorrect answers for others.  I usually copy/paste the UDF from the text file into each new Excel, so I can't see anything in the code having changed.

In the attached Excel, you will find some data exported from CRM, and the UDF, which creats a function called "WorkingHrs".  I use it to calculate the number of business hours between the Created On and Closed On datetimes, counting only hours between 9 AM and 5 PM, Monday-Friday, exluding holidays (which are defined in the Holidays List).  I have highlighted a few examples of CORRECT answers in GREEN and INCORRECT answers in RED.  In cases where the answers are wrong, the answers are either 0 or much higher than they should be.  I have also attached a text file with the UDF code separately.

I'd really appreciate someone more experienced that I with VBA in Excel to see if there is a simple solution to the problem.
Join our community to see this answer!
Unlock 1 Answer and 12 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros