Use Microsoft Flow to create a “today” column for use in SharePoint list calculations

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.

todaydate

Creating the Flow

  1. Set trigger to “Schedule – Recurrence”
    1. Interval: 1
    2. Frequency: Day
    3. Set “time zone” and “hours” (otherwise will run every day at time Flow created)
    4. “Start time” and “minutes” optional
      recurrence.PNG
  2. New step: Get items (SharePoint)
    1. Enter list’s site URL
    2. Select list name
      get items.PNG
    3. If your list has (or will have) more than 100 items, increase the item limit on this step.
  3. New step –> More –> Add an apply to each
    applytoeach
  4. Click within the “select an output” field, then select “value” from the dynamic content dialog
    apply2.png
  5. Click “Add an action”
    add an action.PNG
  6. Update item (SharePoint)
    update item.PNG

    1. Enter site address
    2. Select list
    3. Set ID field to ID
    4. Set Any other required fields to their matching fields in the dynamic content dialog
    5. Set TodayDate (or whatever you named your new today column)
      1. Click within the field
      2. Click “expression” from the dynamic content dialog
      3. Scroll to the “Date and Time” section
      4. Click utcNow()
      5. Click “OK
        expression.png

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.
flowsetdate.PNG

Comments

  1. Pingback: Using current date and/or time as default column value in SharePoint – SHAREPOINT LIBRARIAN

  2. Andrés

    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.

  3. Roger

    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.

  4. Luisa

    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?

    1. SharePoint Librarian

      Hi Luisa, as long as your calculations are calculated column types they will automatically update when the today column updates.

      1. moellerlui

        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?

        1. SharePoint Librarian

          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.

  5. jeanpaul7

    Note that this will change the item’s ‘modified date’ and ‘modified by’ data.

  6. Carlos Alape

    Hi. My task list have a percent completed field. This field in the Update Item, is a requeried. How can I remove it?

    1. SharePoint Librarian

      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.

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

  8. Pingback: Creating a “Today” column in SharePoint that always gives today’s date – SharePoint Librarian

Leave a Reply

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