Formula for sharepoint to calculate hoours

[ikhan_99]

Hello,

Formula works in excel .. but not in sharepoint. Not sure i've posted in correct forum for sharepoint, i viewed similiar thread in this forum.

Need to extract total business hours worked between 2 dates\time (Date and time format)

Challenge i have our clients work any time of the whole week including weekends. i need to extract hours for time worked only during business hours between M-F (9AM -5PM)

Clients provide us Total window worked on project. (Could be 1 hr ,1 day , multiple days) - Require Buisness Hours ONLY in any given start and end date\time

Sharepoint 2010 doesn't support networkdays in calculated values

=ROUND((DATEDIF([Actual Start],[Actual End],"d")*8)-IF(WEEKDAY([Actual Start],2)>=6,0,MIN(MAX(24*(MOD([Actual Start],1)-"9:00"),0),8)) - IF(WEEKDAY([Actual End],2)>=6,0,MIN(24*MAX(("17:00"-MOD([Actual End],1)),0),8)),2)

examples of date/time worked and expected results:

Wednesday, February 01, 2017 1:00 PM Wednesday, February 01, 2017 10:05 PM - Total business hrs worked (3.05)
Thursday, February 02, 2017 9:00 AM Friday, February 03, 2017 12:00 AM - Total business hrs worked (8)
Thursday, February 02, 2017 9:00 AM Thursday, February 02, 2017 5:00 PM - Total business hrs worked (8)

Saturday, January 28, 2017 9:00 AM Saturday, January 28, 2017 5:00 PM - Total business hrs worked (0)

Friday, January 27, 2017 4:00 PM Monday, January 30, 2017 9:00 AM - Total business hrs worked (1)
Friday, January 27, 2017 4:00 PM Monday, January 30, 2017 12:00 AM - Total business hrs worked (4)