0

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]

0

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 "

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.