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