1

I work in financial institution and we want a column in a case management database to show the number of "Buinsess hours" from start to finish of a case. 

Business Hours
Monday to Friday 5am - 8pm

So far I've only been able to get business days. Please note I do not need this field to dynamically update. I just need the answer based on two fields, the "Created" field (which is date and time) and the "last modified" field (Which is also date and time and lists the last action on a case (which would be marking it as completed).

So far I've got this forumula for Number of business days, but now I need hours

=IF(AND((WEEKDAY([End Date],2))<(WEEKDAY(Created,2)),((WEEKDAY(Created,2))-(WEEKDAY([End Date],2)))>1),(((DATEDIF(Created,[End Date],"D")+1))-(FLOOR((DATEDIF(Created,[End Date],"D")+1)/7,1)*2)-2),(((DATEDIF(Created,[End Date],"D")+1))-(FLOOR((DATEDIF(Created,[End Date],"D")+1)/7,1)*2)))
Improve this question
4
  • 4
    I really don't think you're going to be able to do something as complex as business HOURS in a calculated column - are things like event receivers, custom forms or workflow alternatives?
    – Ryan
    Commented Oct 25, 2012 at 12:03
  • 1
    Agree to Ryan!!
    – Arsalan Adam Khatri
    Commented Oct 25, 2012 at 15:53
  • 1
    I did something like this for a ticket resolution project basically as Ryan suggested. I don't recommend using SharePoint OOB features to do this though. If you only need the time lapsed at the end (i.e. not a real-time clock) you can use a JSOM solution that will be a lot simpler (like take the total time lapsed and subtract the non-business hours from it). However, it's when you have public holidays and stuff that it's going to be painful :B. For this you maintain a separate list of PH and use that in your JavaScript.
    – kovac
    Commented Jul 27, 2016 at 9:42
  • 1
    And often the definition of Business Hours should include national holidays and daylight saving times corrections, so both times I have had this requirement we made a web service that provided that number based on start time, end time and country
    – Kasper Bo Larsen
    Commented Apr 21, 2018 at 16:46

2 Answers 2

Reset to default
0

This will get the difference in hours.

=IF(NOT(ISBLANK([End Time])), ([End Time] - [Start Time]) * 24, 0)
Improve this answer
2
  • Thanks for the response Hugh, but that's not business hours on Monday to Friday only? . Anyone else help me with this? As an example, if someone creates a case on Friday at 5pm and completes it on Monday at 7 am . Then number of business hours is 5pm - 8pm Friday = 3 + 5am to 7am on Monday = 2. Total of 5 business hours to resolve a case.
    – Arzan Patel
    Commented Oct 25, 2012 at 8:17
  • Oh okay, I thought you just wanted to know how to calculate hours, this will calculate the difference between two times. You just need 5 of these statements really added together, with the different start end times inputted
    – Hugh Wood
    Commented Oct 25, 2012 at 8:30
0

I did this a long time ago. I don't recall the details, but here are the high level steps:

  1. calculate number of business days x opening hours (15 hours in your case)
  2. subtract the number of hours between 5am and the Created time
  3. subtract the number of hours between the last modified time and 8pm

The assumption is that your SharePoint site is on the same time zone as the business.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.