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.
”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
With your subscription - you'll gain access to our exclusive IT community of thousands of IT pros. You'll also be able to connect with highly specified Experts to get personalized solutions to your troubleshooting & research questions. It’s like crowd-sourced consulting.
We can't always guarantee that the perfect solution to your specific problem will be waiting for you. If you ask your own question - our Certified Experts will team up with you to help you get the answers you need.
Our certified Experts are CTOs, CISOs, and Technical Architects who answer questions, write articles, and produce videos on Experts Exchange. 99% of them have full time tech jobs - they volunteer their time to help other people in the technology industry learn and succeed.
We can't guarantee quick solutions - Experts Exchange isn't a help desk. We're a community of IT professionals committed to sharing knowledge. Our experts volunteer their time to help other people in the technology industry learn and succeed.