Note: I previously shared how to do this in SharePoint Designer. The following method utilizing Flow is better, and does not use loops/pauses.
It’s well-known that SharePoint calculated columns don’t permit [Today] to be used as a formula for a calculated date column. And the “default to today’s date” setting only works upon creation, and doesn’t update daily. But we can create a standard date column and have Microsoft Flow automatically update it daily for us, therefore allowing us to effortlessly perform calculations against today’s date such as:
- Age =(TodayDate-Birthday)/365
- Years of Service =(TodayDate-StartDate)/365
- Days Past Due =(TodayDate-DueDate)
- Weeks until summer break =(SummerStart-TodayDate)/7
Here’s how to create your own, always accurate/updated, today column (see bottom of post for video):
Create a new date column in your list, and set it to Date Only.
Note: Naming the column “Today” has proven problematic for some when using in calculated column formulas. Name it anything but “Today” to be safe.
Creating the Flow
- Set trigger to “Schedule – Recurrence”
- Interval: 1
- Frequency: Day
- Set “time zone” and “hours” (otherwise will run every day at time Flow created)
- “Start time” and “minutes” optional
- New step: Get items (SharePoint)
- Enter list’s site URL
- Select list name
- If your list has (or will have) more than 100 items, increase the item limit on this step.
- New step –> More –> Add an apply to each
- Click within the “select an output” field, then select “value” from the dynamic content dialog
- Click “Add an action”
- Update item (SharePoint)
- Enter site address
- Select list
- Set ID field to ID
- Set Any other required fields to their matching fields in the dynamic content dialog
- Set TodayDate (or whatever you named your new today column)
- Click within the field
- Click “expression” from the dynamic content dialog
- Scroll to the “Date and Time” section
- Click utcNow()
- Click “OK
That’s it! Name your flow, click “Create flow” and wait. If you didn’t enter “Start time” you can watch it run immediately and check the result.