SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It only takes a minute to sign up.
Sign up to join this community
BLUF: I would like to create a calculated column in SharePoint that looks at the [Start Date] and [Provisioned Date] and know how many business days it took between the two.
I am very new to SharePoint and so far I have the following formula that gives me the full count of days between, I just am not sure how to add the BD function into it. Bonus, I would love for it to say either 1 Day(s) and if more than a month, show as X Year, X Months and X Days.
=Provisioned-[Start Date]
Jeanette,
Welcome to the community! 🎉
SharePoint Calculated Columns re-use many of the same functions as can be found in Excel, but there are a lot of Excel functions that were never ported to SharePoint for some reason - probably to keep SharePoint Admins employed. 😎 Here is the formula for the raw number of business days:
Just cut and Paste
=IF(ISERROR(DATEDIF([Provisioned],[Start Date],"d")),"",(DATEDIF([Provisioned],[Start Date],"d"))+1-INT(DATEDIF([Provisioned],[Start Date],"d")/7)*2-IF((WEEKDAY([Start Date])-WEEKDAY([Provisioned]))<0,2,0)-IF(OR(AND(WEEKDAY([Start Date])=7,WEEKDAY([Provisioned])=7),AND(WEEKDAY([Start Date])=1,WEEKDAY([Provisioned])=1)),1,0)-IF(AND(WEEKDAY([Provisioned])=1,(WEEKDAY([Start Date])-WEEKDAY([Provisioned]))>0),1,0)-IF(AND(NOT(WEEKDAY([Provisioned])=7),WEEKDAY([Start Date])=7),1,0))
Anyways, this is the best approximation to what you are looking for in your Bonus Ask. It assumes 262 working days in a year & 21.83 working days per month. It does not account for holidays. You are always going to be off by a day or so each year - not including holidays.
You should be able to cut and paste this into the formula field for your calculated column.
=FIXED(TRUNC(IF(ISERROR(DATEDIF(B2,C2,"d")),"",(DATEDIF(B2,C2,"d"))+1-INT(DATEDIF(B2,C2,"d")/7)*2-IF((WEEKDAY(C2)-WEEKDAY(B2))<0,2,0)-IF(OR(AND(WEEKDAY(C2)=7,WEEKDAY(B2)=7),AND(WEEKDAY(C2)=1,WEEKDAY(B2)=1)),1,0)-IF(AND(WEEKDAY(B2)=1,(WEEKDAY(C2)-WEEKDAY(B2))>0),1,0)-IF(AND(NOT(WEEKDAY(B2)=7),WEEKDAY(C2)=7),1,0))/262,0),0)& " Years " & FIXED(TRUNC(ABS((IF(ISERROR(DATEDIF(B2,C2,"d")),"",(DATEDIF(B2,C2,"d"))+1-INT(DATEDIF(B2,C2,"d")/7)*2-IF((WEEKDAY(C2)-WEEKDAY(B2))<0,2,0)-IF(OR(AND(WEEKDAY(C2)=7,WEEKDAY(B2)=7),AND(WEEKDAY(C2)=1,WEEKDAY(B2)=1)),1,0)-IF(AND(WEEKDAY(B2)=1,(WEEKDAY(C2)-WEEKDAY(B2))>0),1,0)-IF(AND(NOT(WEEKDAY(B2)=7),WEEKDAY(C2)=7),1,0))/262)-TRUNC(IF(ISERROR(DATEDIF(B2,C2,"d")),"",(DATEDIF(B2,C2,"d"))+1-INT(DATEDIF(B2,C2,"d")/7)*2-IF((WEEKDAY(C2)-WEEKDAY(B2))<0,2,0)-IF(OR(AND(WEEKDAY(C2)=7,WEEKDAY(B2)=7),AND(WEEKDAY(C2)=1,WEEKDAY(B2)=1)),1,0)-IF(AND(WEEKDAY(B2)=1,(WEEKDAY(C2)-WEEKDAY(B2))>0),1,0)-IF(AND(NOT(WEEKDAY(B2)=7),WEEKDAY(C2)=7),1,0))/262))*12,0),0) & " Months " & RIGHT(ABS((IF(ISERROR(DATEDIF(B2,C2,"d")),"",(DATEDIF(B2,C2,"d"))+1-INT(DATEDIF(B2,C2,"d")/7)*2-IF((WEEKDAY(C2)-WEEKDAY(B2))<0,2,0)-IF(OR(AND(WEEKDAY(C2)=7,WEEKDAY(B2)=7),AND(WEEKDAY(C2)=1,WEEKDAY(B2)=1)),1,0)-IF(AND(WEEKDAY(B2)=1,(WEEKDAY(C2)-WEEKDAY(B2))>0),1,0)-IF(AND(NOT(WEEKDAY(B2)=7),WEEKDAY(C2)=7),1,0))/262)-TRUNC(IF(ISERROR(DATEDIF(B2,C2,"d")),"",(DATEDIF(B2,C2,"d"))+1-INT(DATEDIF(B2,C2,"d")/7)*2-IF((WEEKDAY(C2)-WEEKDAY(B2))<0,2,0)-IF(OR(AND(WEEKDAY(C2)=7,WEEKDAY(B2)=7),AND(WEEKDAY(C2)=1,WEEKDAY(B2)=1)),1,0)-IF(AND(WEEKDAY(B2)=1,(WEEKDAY(C2)-WEEKDAY(B2))>0),1,0)-IF(AND(NOT(WEEKDAY(B2)=7),WEEKDAY(C2)=7),1,0))/262))*12) & " Days "
