Sharepoint List – Weekdays Since Creation calculation

If you’ve ever run into the Sharepoint error “No volatile functions allowed e.g. TODAY” then you would have been in the same boat as myself.

There’s an intruiging work around for this issue which involves a phantom column and a calculated value column.

Let’s begin:

 

Step 1

Top left view of your list select “List” from the Browse/Items/List menu. Then select Settings:

Sharepoint List settings

At the bottom of that page you can then create new column:

Sharepoint new column date

 

Step 2

Now, select datetime and the default value of “Today”s date. This will be our phantom volatile column, therefore name the column title “Today”.

DateTime Sharepoint Column

Click “OK” to add this column (dont worry, it wont be there for long).

 

Step 3

Repeat the create column step however this time select “Calculated Value” for column type and your preferred name. The following is the code we will insert to calculate Monday-Fridays since List Item creation:

Remember: [Today] is the column name which is recognised by Sharepoint. [Created] is the AUTOMATIC column which denotes when Sharepoint created the list item. This is set for Sharepoint 2013 – change accordingly for your versions!
=IF(AND((WEEKDAY([Today],2))<(WEEKDAY([Created],2)),((WEEKDAY([Created],2))-(WEEKDAY([Today],2)))>1),(((DATEDIF([Created],[Today],"D")+1))-(FLOOR((DATEDIF([Created],[Today],"D")+1)/7,1)*2)-2),(((DATEDIF([Created],[Today],"D")+1))-(FLOOR((DATEDIF([Created],[Today],"D")+1)/7,1)*2)))

 

Select “Number” data type and 0 decimal places, then click “OK”.

Calculated Value Column

 

Step 4

Finally, we can go back to List Settings and click the column “Today” that we created, at the bottom right of the column’s settings there should be a delete option, click that. What this is doing is making sharepoint reference the pointer “Today” to a non-existant today’s date column which then triggers it to calculate the volatile variable “TODAY” anyway!

Delete Today

 

We are done! Enjoy.

Leave a Reply

Your email address will not be published.