# calculate work days between two dates for calculated columns

• ### Question

• Hi,
how to calculate work days(Sunday-Thursday) between two dates using excel formulas,  how should i modify the formula to make workdays as Sun-Thurs pattern

=IF(AND((WEEKDAY([End Date],2))<(WEEKDAY([Start Date],2)),((WEEKDAY([Start Date],2))-(WEEKDAY([End Date],2)))>1),(((DATEDIF([Start Date],[End Date],"D")+1))-(FLOOR((DATEDIF([Start Date],[End Date],"D")+1)/7,1)*2)-2),(((DATEDIF([Start Date],[End Date],"D")+1))-(FLOOR((DATEDIF([Start Date],[End Date],"D")+1)/7,1)*2)))

http://flucidity.co.uk/2015/02/sharepoint-online-holiday-request-system-lists-and-columns/

for use in calculated columns

• Edited by Monday, January 18, 2016 3:27 AM
Sunday, January 17, 2016 7:33 PM

### All replies

• If you have Excel 2010 or later:

=NETWORKDAYS.INTL(A1,B1,7)

Regards, Hans Vogelaar (http://www.eileenslounge.com)

• Marked as answer by Sunday, January 31, 2016 2:48 PM
• Unmarked as answer by Monday, February 1, 2016 5:28 AM
Sunday, January 17, 2016 8:26 PM
• Hi,
In calculated column, this formula don't work , so looking to find a solution something like above shown formula , it works with Sun,Mon as weekend , I am looking how to modify it for Fri,Sat as weekend
Monday, January 18, 2016 3:30 AM
• >>>In calculated column, this formula don't work , so looking to find a solution something like above shown formula , it works with Sun,Mon as weekend , I am looking how to modify it for Fri,Sat as weekend

According to your description, Hans Vogelaar has provided good solution, the NETWORKDAYS.INTL function syntax has the following arguments:

Start_date and end_date    Required.
The dates for which the difference is to be computed. The start_date can be earlier than, the same as, or later than the end_date.

Weekend    Optional.
Indicates the days of the week that are weekend days and are not included in the number of whole working days between start_date and end_date. Weekend is a weekend number or string that specifies when weekends occur.

Weekend string values are seven characters long and each character in the string represents a day of the week, starting with Monday. 1 represents a non-workday and 0 represents a workday. Only the characters 1 and 0 are permitted in the string. Using 1111111 will always return 0.

For example, 0000011 would result in a weekend that is Saturday and Sunday.

• Marked as answer by Sunday, January 31, 2016 2:48 PM
• Unmarked as answer by Monday, February 1, 2016 5:28 AM
Monday, January 18, 2016 6:00 AM
• i GOT solution - but if say both dates (start/end) are on same day- then #REF! error

=IF(AND((WEEKDAY(B3,15))<(WEEKDAY(A3,15)),((WEEKDAY(A3,15))-(WEEKDAY(B3,15)))>1),(((DATEDIF(A3,B3,"D")+1))-(FLOOR((DATEDIF(A3,B3,"D")+1)/7,1)*2)-2),C6(((DATEDIF(A3,B3,"D")+1))-(FLOOR((DATEDIF(A3,B3,"D")+1)/7,1)*15)))

can suggest on this
Tuesday, January 19, 2016 9:47 PM
• >>>i GOT solution - but if say both dates (start/end) are on same day- then #REF! error

According to your description, invalid cell reference errors occur when a spreadsheet formula contains incorrect cell references. This happens most often when:
1.Columns or rows containing data used in the formula are accidentally deleted.
2.A formula has been moved or copied to another cell and the cell references are incorrect.
3.Data used in a formula has been moved, leaving the function or formula with incorrect cell references

So I suggest that if error checking is turned on in Excel, click the button that appears next to the cell that displays the error Button image, click Show Calculation Steps if it appears, and then

click the resolution that is appropriate for your data.