There’s a well known SharePoint trick that allows you to use [Today] (the current date) in calculated columns. You’ll find several posts that explain it, here is for example a recent one:
There are some limitations to this trick:
-You have to recreate the dummy [Today] column every time you need to edit the calculated column(s).
-The calculation is “static”. Do not expect the value to automatically refresh everyday, it will only be updated the next time you edit the list item.
So you should actually interpret “Today” as the date when the item was last modified.
But wait… isn’t there a column that already provides this value? Why not just use the [Modified] column? Let’s compare these two values on a simple example, using the tasks list from my previous post.
The formulas below calculate the number of days left until the due date:
- using the “Today” trick: =[Due Date]-[Today]
- using the default [Modified] column: =[Due Date]-[Modified]
The result (today is August 14th):
There’s a slight difference, this is because [Today] provides the current date at 12 am, while [Modified] provides the current date and time.
To replicate the behavior of the [Today] column, we just need to round down the [Modified] date:
My conclusion: don’t waste time with the “Today” trick in your calculated columns, simply use:
- Either the [Modified] column to get the date and time
- Or ROUNDDOWN([Modified],0) which behaves the same as the “Today” trick
In a future post I’ll explain my method to get today’s date…everyday!