Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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)))
share|improve this question
2  
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 Oct 25 '12 at 12:03
    
Agree to Ryan!! –  Arsalan Adam Khatri Oct 25 '12 at 15:53
add comment

1 Answer 1

This will get the difference in hours.

=IF(NOT(ISBLANK([End Time])), ([End Time] - [Start Time]) * 24, 0)
share|improve this answer
    
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 Oct 25 '12 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 Oct 25 '12 at 8:30
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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