locked
Calculated Sharepoint Column: calculating Workdays (Sat - Wed), excluding Weekends (Thursday - Friday) RRS feed

  • Question

  • Hi,

    I've tried many formulas but I can't seem to make it work.

    Is there a way to calculate the weekdays (Sat, Sun, Mon, Tues, Wed) between 2 dates (Start Date and End Date) and excluding the Weekends (Thursday and Friday). 

    I've tried the following:

    =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)))&" day(s)"

     

    and this:

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

     

    It doesn't really give the correct no of days, specially if the Start date is Wednesday and the End date is Sat or Sun.

    • Moved by Mike Walsh FIN Sunday, January 22, 2012 1:22 PM A caculated column is a standard function so this is admin not customization (From:SharePoint - Design and Customization (pre-SharePoint 2010))
    Sunday, January 22, 2012 1:07 PM

Answers

All replies