Solved

How to get number of WORKDAYS between two dates in SharePoint calculated column

Posted on 2011-09-21
3
4,864 Views
Last Modified: 2012-05-12
I need to count the number of WORKING days between two dates in a calculated column in SharePoint 2007. Does anyone know of a formula to do this? I know that "=DATEDIF(Column1, Column2,"d")" provides the total number of days, but the customer wants only the number of WORKING days, i.e. M-F. Excel has a formula for it...can't seem to find one for SharePoint though. Anybody?
 
0
Comment
Question by:cjones_mcse
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 4

Accepted Solution

by:
leopolde earned 500 total points
ID: 36577662
SharePoint 2007 doesn't provide a direct function to achieve what you need.

You can try the following formula in a calculated field:
=DATEDIF([Start Date],[Due Date],"D")-IF(WEEKDAY([Due Date])=7,FLOOR((DATEDIF([Start Date],[Due Date],"D")+WEEKDAY([Start Date]))/7,1)*2,FLOOR((DATEDIF([Start Date],[Due Date],"D")+WEEKDAY([Start Date]))/7,1)*2+1)+IF(WEEKDAY([Start Date])=7,2,1)
 
Or here is a shorter version, that I haven't tested as thoroughtly as the first one:
=DATEDIF([Start Date],[Due Date],"D")-FLOOR((DATEDIF([Start Date],[Due Date],"D")+WEEKDAY([Start Date]))/7,1)*2-IF(WEEKDAY([Due Date])=7,0,1)+IF(WEEKDAY([Start Date])=7,2,1)
0
 
LVL 4

Expert Comment

by:leopolde
ID: 36592667
Did the formulas work for you?
0
 
LVL 10

Author Comment

by:cjones_mcse
ID: 36686916
I think they're going to work. Just having some trouble getting my formulas to ignore empty date fields and invert dates that end up being negative numbers. It's not always a start/end date, but the days between two board meetings which can occur before or after each other, i.e., board1 convenes on 2/1/11 and board2 convenes on 3/1/11 for item 1, but board1 convenes on 4/1/11 and board2 convenes on 3/1/11 for item 2. The formula for item 2's calculated column gives me a #NUM! error because the returned value is less than 0. Anyway, that wasn't part of my question and your answer does what I asked, so points to you! Thanks!
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How complex is implimenting global search for SharePoint 2013 2 53
Sharepoint 2016 LDAP connection 22 91
REST call Failing 1 41
SharePoint 2013 Search not working 9 74
There is one common problem that all we SharePoint developers share: custom solution deployment. This topic can't be covered fully in this short article, so all I want to do in this one is to review it from a development-to-operations perspectiv…
In case you ever have to remove a faulty web part from a page , add the following to the end of the page url ?contents=1

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question