count workdays

[JW]

I am new.  This site has been very helpful.  I have searched and not found an exact soultion. I need to count workdays.  Can you do this in a calculated column?  I have 2 date fields and the calculated coulmn gives the total days, but I only want workdays.  No weekends.  Is there a workday function that would do this. Or a simple workflow.  I tried run if  fn(date, "dddd" was not equal "S" for Sat or Sun.  but did not get the correct answwer.  I saw the UDA but have not been able to get that to work.

[barryc]

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

[kunalpatel · Accepted solution]

@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))

[SimonMuntz]

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.

[JW]

Your awesome, it worked like a charm thank you.