0

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)
Improve this question

1 Answer 1

Reset to default
0

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.

http://www.viewmaster365.com/365coach/#/Calculated_Column_Functions_List

are tested en tried Functions that do work in SharePoint

Improve this answer
2
  • Thanks for info... New to sharepoint and kindly assist in converting above excel formula to sharepoint 2010 format
    – Newbee
    Commented Feb 18, 2017 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
    – Danny '365CSI' Engelman
    Commented Feb 19, 2017 at 9:02

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.