Update 12/8/17: There’s a better way to do this using Microsoft Flow, if that’s available to you.
You may, at some point, find yourself working with calculations among dates, including “today’s date” which conceptually seems simple but requires a bit of work to function correctly. You may have even created a “today” column that defaults to “current date” or attempted a calculated column only to find that the date will not automatically update each day or that calculated columns cannot show dynamic data like that. Fret no more.
Today columns are essential for use in calculated columns that tell you things like “days until event”, “days without incident” or “years of service” without needing to click any buttons or take any additional steps. Here are some ideas for how you can use the “Today” column in calculations. We’re going to create our solution via SharePoint designer workflow and a new Today column.
Creating an accurate “Today” column
Our workflow will set a “today” column to today’s date, but we’ll modify the workflow to repeat, pausing for 12 hours at a time, so that we always have “Today” as today’s actual date. If we don’t set a sort of timer, it will only update “Today” when the list item is created or modified (or if we run the workflow manually).
- Create a “today” date-format column in your list.
Note: You will probably want to keep this column “hidden” in your content types and taken off your views since it will primarily be used in calculations for other columns.
- Open SharePoint Designer and create a workflow on your list
- For your action, select “Update a list item” or type “update” and press enter
- Click “this list” and “add”
- Choose your “Today” field, then the ellipses on the next field so you can select “current date” and ok
- Click Ok Again
- Type “pause” and enter and select “pause for duration.
Note: You may prefer a different way of scheduling the regular update, such as loops, so feel free to improvise.
- Change hours to 12 hours (or however often you want it to update today’s date)
- Change “transition to stage” to say “go to” and select “stage 1” so once the workflow runs it begins again based on the time you’ve set it to pause in between updates
- Add some status updates so you can easily see your workflow status history. I suggest an “initiated” status update in the beginning and a “last updated xx/xx” status after the update
- Your workflow should resemble this:
- On the workflow settings page, set the workflow to run manually, on creation and on edit so you’re always guaranteed to have an up-to-date today column.
- Publish your new workflow.