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.
Hi! This post is great and helped me with my first SP list that uses a calculated column that requires today’s date. The workflow seems to work…but only for the first item in my list. Is there a way to loop this workflow through all items in the list? If it was mentioned, I may have missed it.
Hi there I have a question, this is great! I’m using it now but was wondering if there’s a way for “Today” to auto-populate on all new items, instead of me going in and putting them manually?
This is exactly what I expected…is there no way to have it updated automatically?
Does this update the modified field every time ?
I’m trying to get it so that I can find how long a document has not been modified for and then format the row or a field to turn red. showing that action must be taken say after 90 days ?
I originally had =DATEDIF([MODIFIED]Now(),”d”).So i would get a column of the days, and i could format via that,but as i found it doesn’t run daily. And for the life of me i can’t format a cell from the modified field alone.
This does update the modified field unfortunately. Perhaps you could create a checkbox to indicate modified by a person and after 90 days, workflow unchecks the box setting off that condition and requiring review.
Is it okay to use this to 30K records
With that many records, I would recommend trying my solution with Flow instead. It wouldn’t constantly be looping then. But even this solution isn’t resource intensive. Might copy your list to a test environment and try it there first.
Hi Nate
Thank you post this to help solve Today() in calculated column issue, I have question like this:
it looks this flow related to each item, my list now have 1000 records only, but it will become 10,000 future, each 6 hours It refresh the workflow, so it should be 240,000 times update in this single list each day.
Does this can happened as I think?
I haven’t tried this on a list that large, but theoretically you’re correct.
By the way, how to stop this flow, it looks it always loop!
You can stop all instances of the flow by going to list settings, workflow settings, remove/block/restore a workflow, mark “remove” next to the workflow and click OK. You can always publish it again later to add it. You might consider using Flow instead to do this. Create a basic date column, create a Flow with a recurrence trigger daily to update the date column with today’s date. Those don’t loop, so might be a better option for you. I’ll write a post on it soon.
Here’s a better way to have a today column, using Microsoft Flow: https://sharepointlibrarian.com/2017/12/08/use-microsoft-flow-to-create-a-today-column-for-use-in-sharepoint-list-calculations/
If you use the ‘Today’ date in a calculated column. Will the calculated column be refreshed everyday?
E.g I have a DueDate with a date for instance 17/10/2017.
If I then use this column with today column in a calculated field like =[DueDate]-[Today] then I’ll get 4 days.
If the workflow run at midnight is it correct the result of the calculated column will be 3 days tomorrow?
Because I thought calculated columns will only update on creation and edit.
Best regards
Morten
Hi Morten,
Because the workflow forces an update every day, the associated calculated columns will update as well. So yes, the result tomorrow should show one day less for your due date.
You can’t use the volatile [Today] variable in a calculated column in SharePoint. It’s a bummer, I know.
You can, however, use Today() which does the same thing: https://wp.me/p7W3RV-1ei
The only difference is that won’t auto-update. It only calculates on creation and edits.
The blog post you’ve commented on requires creating a new date column and using Flow to set its value to the current date each day. That’s the best way to have a column for today that’s always accurate and doesn’t need updating. It also updates any calculated columns on each item since essentially it’s editing the item.
Am I correct that while this workflow is in the pause stage if I change the date for an item it will not change immediately but will have to wait for 12 hours?
Hi Sohail, changes made while the workflow is paused will show immediately. The workflow is only making sure your “today” column is always accurate, and won’t affect anything else.
Thanks for your response. I recently was required to create a workflow with regular reminders e.g. wait 24 hour and send a reminder. I noticed that if workflow is in the pause stage it won’t move out. This is strange especially when workflow is required to run on updated item also. Do you have any documentation which confirms that pause will terminate if changes are made on the item? Thanks
Hi Sohail, I hope I’m understanding your scenario correctly. I’m assuming you’re doing a 2013 SPD workflow. So in your “transition to stage” area, you can do a conditional statement. Delete what you have in there (such as “Go to Stage 1” or “Go to End of Workflow”) and type “If” to begin an if/then statement. You can then say if a condition is met (a field is set to “response received,” for example) you can “go to end of workflow” else “go to stage 1” to start the “loop” over again until the specific change is made that would make it so you no longer require 24-hour reminders and ends the workflow.
These workflow hacks while clever, show case the weakness of SharePoint calculated fields and that it lacks a timer job for workflow processing. Features like a real-time calculated field are not hard to implement, SQL Server does that on the back end successfully already, so why can’t SharePoint too? (Yes, SharePoint is a Database within a database, which limits it’s ability but we are now at version 5, stuff like this should be solved by now.)
And lastly not having the ability to natively schedule workflow is a critical missing piece. (Don’t get me started on the complete abandonment of the existing workflow engines by MS.) Having to “pause and loop” a workflow in order to get some semblance of scheduling is resource intensive and hackish. It’s frustrating that MS hasn’t solved this issue yet or provide a road map of the future for the workflows. MS Flow is not ready yet to replace the integrated workflow engines.