Solved

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

Posted on 2011-09-21
3
4,497 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
  • 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

If you create your solutions on SharePoint sooner or later you will come upon a request to set  permissions of the item depending on some of the item's meta-data - the author, people assigned as approvers, divisions, categories etc. The most natu…
The vision: A MegaMenu for a SharePoint portal home page The mission: Make it easy to maintain. Allow rich content and sub headers as well as standard links. Factor in frequent changes without involving developers or a lengthy Dev/Test/Prod rel…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

803 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