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):
Prep
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.
Hi Nate,
can you use this for files?
Dave S
Crazy that SP has no out-of-box function to automatically update each day with that day’s date, so thank you for this. However, the Flow somehow renders my yes/no column filter useless. So while the calculated column w/ the Flow now works, the List View is wrong. Any ideas?
Now that calculated columns allow the use of TODAY() you should put a note at the top of your post saying it’s obsolete. Of course calculated columns don’t recalculate until something in the item is changed, so maybe having a throwaway column like Today is still a worthwhile thing to do. Just don’t let people think the value in there is important in any way – you could also update any other column and it would have the same effect.
Probably easier to just have a calc column with =TODAY() and a flow that updates any other field once a day. The problem with that for pages is it puts the pages back in draft mode everyday, so they all have to be republished. Not ideal.
If you are using TODAY() for this Today column and then using the Today column to calculate other columns … why not just use TODAY() to calculate those other columns? Of course you still have to update SOMETHING in the item on a daily basis or the calculated columns will not be recalculated.
Hi, Thank you for the nice post. It is very helpful, however, I am having issues where TodayDate column update once a day or if you run the flow manually, for example if I create an item, TodayDate column is empty until either run flow manually or wait for flow to run on schedule, would you please explain little, thank you
Hi Nate,
This really help me a lot as we are trying to have an auto -updated “”today’s date” to calculate how many days left until the “Due-date”.
One problem I came across is: the auto-update did change the column “Today’s date”, but it also changed other column data. So in Microsoft Flow I simply left all other column data blank, then I found out all these column data changed to blank after I ran the flow.
There must be something run with my setting, can you please advise?
Really appreciate your help!
It shouldn’t replace the existing fields with a blank. As far as I know and experienced, only the field with changes will apply to update list. But if this is a new function of Flow, then the workaround is to add the dynamic content of each field from Get Items action so that the fields will be the same except for the Today’s date. Let me know if this works for you.
Add a get items action and update the other items with the current value from Get items. Then use the date as described above to populate the today field
Hello from Colombia!
I appreciate your tutorial because there’s not enough information about Microsoft Flow in Spanish. I’m trying to do the same thing but in libraries where people can upload files. Could you explain me if it is possible? Step 2 talks about Get List Items but I’m needing Libraries Files and I couldn’t find that action.
Hope you can help me.
Hi, the flow works great so far, I’m getting today’s date returned! My issue is trying to calculate the number of weeks between two dates with it now, I’m getting some crazy numbers coming back. Basically, I want to calculate the weeks between today’s date and the first available date (this was entered when the item was created). My Calculated field right now is: =((TodayDate-[FirstAvailableDate])/7), but its not returning anywhere near the right amount. I’ve tried to just do =TodayDate-[FirstAvailableDate] and that doesn’t even return the right number of days between the two dates. I’ve also tried =DATEDIF(TodayDate,[FirstAvailableDate,”d”) and that still doesn’t return the correct days between the two dates.
Any help would be appreciated.
Hi Nate 🙂 This will automaticcaly update todays date… but will it also automatically update the calculated value? (e.g. the years of service)? I’m wondering how to do this. Would I have ti recreate the same flow for the actual calculation im trying to achieve?
Hi Luisa, as long as your calculations are calculated column types they will automatically update when the today column updates.
Hi Nate, Thanks for your quick answer! sounds good. DO you know if theres a possibility to perform this only on certain elements? Cause i only got an Essentials Plan in my use case, but a relatively large lists (20k elements) and 5 users, which makes 10k flow runs per month (so only half of the data gets updated with the current date). I have to perform your flow once a month, but not on all elements. Maybe you have an idea on this one too?
My understanding is since it’s still just one list, and the flow runs through all items in the list, it’s counted as one single run each time it goes. With that large of lists, the flow may struggle to complete correctly. Sometimes I’ll have it run twice, once at 1:00am and again at 3:00am just in case, and you can set the flow to have an additional step to skip the item if the date is already “Today”. It’s not a perfect solution, but an idea.
Note that this will change the item’s ‘modified date’ and ‘modified by’ data.
Yes it will
Hi. My task list have a percent completed field. This field in the Update Item, is a requeried. How can I remove it?
I have tried removing the field as well (by going into settings for the task list, allowing management of content types, then editing the “SP 2013 task” or whichever you’re using to not show the percent completed), but it also removed the approval/reject buttons. You can play around with content types, though, and perhaps use some css to hide the percent completed field.