Advertisements

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

Update 12/8/17: There’s a better way to do this using Microsoft Flow, if that’s available to you.

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

  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.

Advertisements

24 Comments

  1. Anhony Baratta

    Reply

    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.

  2. Pingback: Date calculations using “Today” in SharePoint lists for years of service, days without incident, etc. (includes using blank date values) – SharePoint Librarian

  3. Sohail

    Reply

    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?

    • Reply

      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.

      • Sohail

        Reply

        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

        • Reply

          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.

  4. Pingback: Show last modified date for a SharePoint list without using code – SharePoint Librarian

  5. mortenruus

    Reply

    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

    • Reply

      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.

    • Jesse MacDonough

      Reply

      You can’t use the volatile [Today] variable in a calculated column in SharePoint. It’s a bummer, I know.

      • SharePoint Librarian

        Reply

        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.

  6. Pingback: Generate and send reports, files or lists regularly with Microsoft Flow’s “recurrence” trigger – SharePoint Librarian

  7. Philip

    Reply

    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?

  8. Pingback: Use Microsoft Flow to create a “today” column for use in SharePoint list calculations – SharePoint Librarian

  9. Pingback: Using today’s date and/or current time in calculated columns and list view filters – SharePoint Librarian

    • Reply

      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.

  10. Alex

    Reply

    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.

    • Reply

      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.

  11. ItSnows

    Reply

    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?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: