# 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

• 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 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 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.

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