I have a field that is called Transmit Date, I need to generate a calculated column 21 business days from the date of this field. I have seen some other posts that fall within a week. I am curious how I set this up for 21 business days.
1 Answer
Please follow steps:
1.Create a Date and Time column named "Test_Data"
2.Then, create a Calculated column called "Transmit Date" and copy the following formula to the Calculated column：=IF(WEEKDAY(Test_Data)=1,Test_Data+21+2*4,IF(WEEKDAY(Test_Data)=6,Test_Data+21+2*5,IF(WEEKDAY(Test_Data)=7,Test_Data+21+2*4+1,Test_Data+21+2*4)))
Note: The “21” represents the number of working days
3.The Final Display：
====================== Updated Answer =======================
By default, users need add square brackets ( [ ] ) for Column Name.
Eg. =[Column1]+[Column2]
More information, please refer to Calculated Field Formulas.
Calculated column formula:
=IF(WEEKDAY([Transmit Date])=1,[Transmit Date]+21+2*4,IF(WEEKDAY([Transmit Date])=6,[Transmit Date]+21+2*5,IF(WEEKDAY([Transmit Date])=7,[Transmit Date]+21+2*4+1,[Transmit Date]+21+2*4)))
answered Jan 20, 2021 at 10:29
-
Thank you for looking at this. Still having trouble. In your example above your Test_Data is my Transmit Date and my Transmit Date is called Due Date. When putting this into the SharePoint list I get an error. I modified in one instance putting square brackets around Transmit Date bcs that seems to be how it usually wants the calculation of a field. However, I tried both ways: =IF(WEEKDAY[Transmit Date]=1,[Transmit Date]+21+2*4,IF(WEEKDAY[Transmit Date]=6,[Transmit Date]+21+2*5,IF(WEEKDAY[Transmit Date]=7,[Transmit Date]+21+2*4+1,[Transmit Date]+21+2*4))) - Any suggestions?– ElaineCommented Jan 22, 2021 at 11:42
-
Hi, Elaine. Thanks for your reply. I have updated my answer, hope this can help you. Have a nice day! Commented Jan 25, 2021 at 9:06