troubleshooting Question
Calculation of business hours between two datetimes in Excel
I_play_with_DNA asked on
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.
EE-Resolution-Times.xlsm
working-hours-UDF.txt
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.
EE-Resolution-Times.xlsm
working-hours-UDF.txt
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.
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Most Valuable Expert award recognizes technology experts who passionately share their knowledge with the community, demonstrate the core values of this platform, and go the extra mile in all aspects of their contributions. This award is based off of nominations by EE users and experts. Multiple MVEs may be awarded each year.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.