Creating a “Today” column in SharePoint that always gives today’s date

update7

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. Your list’s calculated columns using your new Today column will always accurately reflect calculations using the current date. 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).

  1. 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.

  2. Open SharePoint Designer and create a workflow on your list
    TodaysDateWorkflow
  3. For your action, select “Update a list item” or type “update” and press enter
  4. Click “this list” and “add”
    update1
  5. Choose your “Today” field, then the ellipses on the next field so you can select “current date” and ok
    update2
  6. Click Ok Again
    pdate3
  7. 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.

  8. Change hours to 12 hours (or however often you want it to update today’s date)
    pdate5
  9. 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
  10. 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
    update6
  11. Your workflow should resemble this:
    update7
  12. 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.
    update8
  13. Publish your new workflow.

6 thoughts on “Creating a “Today” column in SharePoint that always gives today’s date

  1. 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.

    Like

  2. 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?

    Like

      1. 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

        Like

        1. 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.

          Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s