count workdays

Hi JW,

Something I have done before is to create a list in my site that stores sequential work days. You can then lookup that list to determine the number of work days by subtracting the list item ID for date2 from date1. You can create the list of days in Excel then import it into a SharePoint list, so conceivably you could import years at a time.

e.g. in the table below I have added 2 weeks worth of work days...Tue 5/11 was a public holiday here, hence I haven't included it.

So if my dates are 4/11 - 7/11 (dd/MM by the way) I calculate workdays as 3-1 = 2 days. Or potentially (3-1)+1 if they are counted as whole days.

You should be able to look up this list either in the form (lookup function) or in the workflow (query list).

@JW ....try below formula in calculated column (I've used created date as start date and modified date as end date):

=IF(ISERROR(DATEDIF([Created],[Modified],”d”)),””,(DATEDIF([Created],[Modified],”d”))+1-INT(DATEDIF([Created],[Modified],”d”)/7)*2-IF((WEEKDAY([Modified])-WEEKDAY([Created]))<0,2,0)-IF(OR(AND(WEEKDAY([Modified])=7,WEEKDAY([Created])=7),AND(WEEKDAY([Modified])=1,WEEKDAY([Created])=1)),1,0)-IF(AND(WEEKDAY([Created])=1,(WEEKDAY([Modified])-WEEKDAY([Created]))>0),1,0)-IF(AND(NOT(WEEKDAY([Created])=7),WEEKDAY([Modified])=7),1,0))

Hi,

The attached workflow example uses the method of not counting Saturday or Sunday.

It will calulate from the current date up to what ever date you set in the first action but ofcourse you can tailor this to your solution. It outputs to the History list for POC purposes.

Your awesome, it worked like a charm thank you.