Advertisements

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

Advertisements

12 Comments

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

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

  3. Reply

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

    • Reply

      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.

  4. Reply

    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?

      • moellerlui

        Reply

        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?

        • Reply

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

    Reply

    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.

  6. Andrés

    Reply

    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.

Leave a Reply

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

%d bloggers like this: