There has been much discussion around using a [TODAY] column in SharePoint Lists since SharePoint Lists and Libraries have been around. Other than being able to use [Today] to set a DEFAULT value, users have always asked if they could use it in a calculated field. There are workarounds well documented in various threads that detail creating a field called “Today”, then creating a calculated field ([CalculatedToday]) that references the “Today” field, and finally removing the “Today” field in order to be able to use “[Today]” in a calculated field.
The question that follows is always, “how do I get the [CalculatedToday] to update every day or on a scheduled basis?” Again, there are numerous articles documented on the internet that suggest adding and removing a [Today] field and SharePoint will update all the [CalculatedToday] fields with the current Date. Administrators have created scripts and even timer jobs to do this automatically. This method works great until the List gets to a certain size and no longer works.
To assist with this common request, I’ve attached a small console utility that will:
- Initially create a [CalculatedToday] field to any List specified and update it with Today’s Date
- Update any List step 1 was run on and update the [CalculatedToday] field with today’s date. This can be ran on a Server Task Scheduler or on any PC running .NET
- Reset all the [CalculatedToday] fields to the default date
The [CalculatedToday] field can then be used in any another calculated field formula. This utility will not modify the Modified Date field (I am not mass updating the fields in the list).
To use this utility, download the zip attachment here or below and run the commands replacing the site url and list name. Example commands are as follows with respective relation to the numbered list above.
- SurfPoint.SharePointHelper.exe -createcalculatedtoday https://dev.surfpointtech.com “MyList” – will only need to be ran once per list
- SurfPoint.SharePointHelper.exe -refreshcalculatedtoday https://dev.surfpointtech.com “MyList” – run as many times needed (once a day, once a month…etc)
- SurfPoint.SharePointHelper.exe -resetcalculatedtoday https://dev.surfpointtech.com “MyList” – will reset to dates to 12/30/1899 (for testing so you don’t have to change dates to see it working)
Note: Ensure that the user running the utility has access to modify the respective List. This will also work with SharePoint on Office365 the 4th and 5th arguments would be your Office365 login and password
Hi Don, this script is promising, however I am getting an error on the username parameter for o365. Is this a problem with the exe or with how I am passing in the params for username and password.
c:\ConsoleUpdater>SurfPoint.SharePointHelper.exe -createcalculatedtoday “https:/
/agency.sharepoint.com/sites/division/xyz/” “Ticket Prioritization” “myemail@myagency.gov” “myusername”
Unhandled Exception: System.ArgumentException: The ‘username’ argument is invalid.
at Microsoft.SharePoint.Client.SharePointOnlineCredentials..ctor(String username, SecureString password)
at SurfPoint.SharePointHelper.CalculateToday.AddRemoveField.CreatedCalculated
Today()
at SurfPoint.SharePointHelper.Program.CreateCalculatedToday(String[] args)
at SurfPoint.SharePointHelper.Program.Main(String[] args)
Ever wonder how you can calculate the age of the list items in SharePoint? The Today() function is not allowed in calculated fields, so what’s the trick?
1. Go to the list where you want to calculate age of the items e.g. Task List
2. Click Settings => List Settings and create a new column and name it e.g. “Today”, There is no need to select any specific type for this column (Just type the name of the column and click “OK” at the bottom)
3. Create one more column and name it e.g. “Age in Days”. Select “Calculated(calculation based on other columns)” type and provide formula using “Today” column which you created in step 2 e.g. I have entered
=[Today]-[Created]. Select “number” for the data type and 0(zero) in “Number of Decimal places”. So basically it will calculate difference between today’s date and date the item was created.
4. After 3rd step, remove “Today” column, which you have created in step 2. When you remove Today column, SharePoint automatically refers “Today” (which has been deleted now) as the today’s date in the formula calculation.
5. Go back to Task List and it should display the number of days under the “Age in Days” column of each items in Task List.
Hi, this program is working? i am interested