locked
Caculated column formula for Workdays between two dates ? Excluding weekends? DateDiff

    Question

  • This gives me the number of days between two days.. great

    =DATEDIF([Completed],[Issued],"D")

    is there any way to exclude weekends?
    Thursday, July 02, 2009 7:10 PM

Answers

  • You can use the following without having to do the "Today trick":

    =IF(AND((WEEKDAY(Completed,2))<(WEEKDAY(Issued,2)),((WEEKDAY(Issued,2))-(WEEKDAY(Completed,2)))>1),(((DATEDIF(Issued,Completed,"D")+1))-(FLOOR((DATEDIF(Issued,Completed,"D")+1)/7,1)*2)-2),(((DATEDIF(Issued,Completed,"D")+1))-(FLOOR((DATEDIF(Issued,Completed,"D")+1)/7,1)*2)))
    • Marked as answer by cyberpine.com Thursday, July 02, 2009 8:25 PM
    Thursday, July 02, 2009 7:36 PM

All replies

  • You can use the following without having to do the "Today trick":

    =IF(AND((WEEKDAY(Completed,2))<(WEEKDAY(Issued,2)),((WEEKDAY(Issued,2))-(WEEKDAY(Completed,2)))>1),(((DATEDIF(Issued,Completed,"D")+1))-(FLOOR((DATEDIF(Issued,Completed,"D")+1)/7,1)*2)-2),(((DATEDIF(Issued,Completed,"D")+1))-(FLOOR((DATEDIF(Issued,Completed,"D")+1)/7,1)*2)))
    • Marked as answer by cyberpine.com Thursday, July 02, 2009 8:25 PM
    Thursday, July 02, 2009 7:36 PM
  • I have used this formula to display quantity of days between a promised delivery date and actual delivery date. However sometimes the delivery date is prior to the promised date. When this happens the result displays #NUM. Is there anything that can be done with this formula to correct this?

    The ideal result would be to make a negative number return as zero.

    Thanks in advance
    Wednesday, January 27, 2010 10:23 AM
  • Hi Mchova,

    is the 2 in the code for 2 days?

    i'm trying to use your code to calculate weekdays between 2 column named start time and end time.

    can i modify your code to calculate weekdays without weekends?

    Thursday, September 16, 2010 1:36 PM
  • Hi Jenfifi,

    You can modify by replacing "Completed" with with your end date and "Issued" with Start Date.

    To get complete break up of formula see this link:

    http://www.endusersharepoint.com/2009/05/28/taming-the-elusive-%E2%80%9Ccalculated-column%E2%80%9D-date-and-time-part-1/

    Thanks


    Dare to promise
    Wednesday, October 27, 2010 11:17 AM
  • Thanks!  This worked perfectly. 

    I just had to replace 'Issued' with [Start Date] and 'Completed' with [End Date] column names on my list.

    Friday, January 28, 2011 6:58 PM