Trying to create column in list settings using calculated field based on other columns in SharePoint 2010

Using formula :

=ROUND((NETWORKDAYS([Actual Start],[Actual End])*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)

Gives me an error.

Formula works in excel!!!

Can anyone assist in having a look at the formula and point if any errors? or correction read.

Basically - Trying to calculate the OFF business hours from a given start and end time

Weekday OFF business hours (mon -Fri ) 5pm - 9am
Weekend OFF business hours = Friday 5pm to Monday 9AM

Currently below columns exist:

  • Actual Start
  • Actual End
  • Total Duration
  • Off business hours (yet to create) (Total hours - Business hours)

Formula works in excel!!!

SharePoint is not Excel, many simple Functions are the same,

But complex functions do not exist in SharePoint because there is no concept of a A1:A6 range in SharePoint...
It looks like Microsoft only ported Functions that didn't do anything Excel-ish.. one NETWORKDAYS parameter can be a range.. so they did not port the code.


are tested en tried Functions that do work in SharePoint

  • Thanks for info... New to sharepoint and kindly assist in converting above excel formula to sharepoint 2010 format
    – Newbee
    Feb 18 '17 at 16:58
  • well, Microsoft did not convert NETWORKDAYS, so its functionality is not easily replicated. Rough approach would be to substract 2 days for each 7 days Feb 19 '17 at 9:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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