Microsoft 365 & Power Platform Training and Consulting

Contact

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

—

1–2 minutes

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

Discover more from Nate Chamberlain

Subscribe to get the latest posts sent to your email.

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

  1. Dave Avatar
    Dave

    Hi Nate,
    can you use this for files?
    Dave S

    Reply
  2. cathy Avatar
    cathy

    Crazy that SP has no out-of-box function to automatically update each day with that day’s date, so thank you for this. However, the Flow somehow renders my yes/no column filter useless. So while the calculated column w/ the Flow now works, the List View is wrong. Any ideas?

    Reply
  3. Peter Bowers Avatar
    Peter Bowers

    Now that calculated columns allow the use of TODAY() you should put a note at the top of your post saying it’s obsolete. Of course calculated columns don’t recalculate until something in the item is changed, so maybe having a throwaway column like Today is still a worthwhile thing to do. Just don’t let people think the value in there is important in any way – you could also update any other column and it would have the same effect.

    Reply
  4. webbrewers Avatar
    webbrewers

    Probably easier to just have a calc column with =TODAY() and a flow that updates any other field once a day. The problem with that for pages is it puts the pages back in draft mode everyday, so they all have to be republished. Not ideal.

    Reply
    1. Peter Bowers Avatar
      Peter Bowers

      If you are using TODAY() for this Today column and then using the Today column to calculate other columns … why not just use TODAY() to calculate those other columns? Of course you still have to update SOMETHING in the item on a daily basis or the calculated columns will not be recalculated.

      Reply
  5. Imran Avatar
    Imran

    Hi, Thank you for the nice post. It is very helpful, however, I am having issues where TodayDate column update once a day or if you run the flow manually, for example if I create an item, TodayDate column is empty until either run flow manually or wait for flow to run on schedule, would you please explain little, thank you

    Reply
  6. Kyle Yao Avatar
    Kyle Yao

    Hi Nate,

    This really help me a lot as we are trying to have an auto -updated “”today’s date” to calculate how many days left until the “Due-date”.

    One problem I came across is: the auto-update did change the column “Today’s date”, but it also changed other column data. So in Microsoft Flow I simply left all other column data blank, then I found out all these column data changed to blank after I ran the flow.

    There must be something run with my setting, can you please advise?

    Really appreciate your help!

    Reply
    1. Lexa Avatar
      Lexa

      It shouldn’t replace the existing fields with a blank. As far as I know and experienced, only the field with changes will apply to update list. But if this is a new function of Flow, then the workaround is to add the dynamic content of each field from Get Items action so that the fields will be the same except for the Today’s date. Let me know if this works for you.

      Reply
    2. Robert Redmayne Avatar
      Robert Redmayne

      Add a get items action and update the other items with the current value from Get items. Then use the date as described above to populate the today field

      Reply
  7. Using current date and/or time as default column value in SharePoint – SHAREPOINT LIBRARIAN

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

    Reply
  8. Andrés Avatar
    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.

    Reply
  9. Roger Avatar
    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.

    Reply
  10. Luisa Avatar
    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?

    Reply
    1. SharePoint Librarian Avatar
      SharePoint Librarian

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

      Reply
      1. moellerlui Avatar
        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?

        Reply
        1. SharePoint Librarian Avatar
          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.

          Reply
  11. jeanpaul7 Avatar
    jeanpaul7

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

    Reply
    1. SharePoint Librarian Avatar
      SharePoint Librarian

      Yes it will

      Reply
  12. Carlos Alape Avatar
    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?

    Reply
    1. SharePoint Librarian Avatar
      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.

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

    […] previously shared how to create a “Today” column in SharePoint that would always be up-to-date even if list items weren’t modified. These were […]

    Reply
  14. Creating a “Today” column in SharePoint that always gives today’s date – SharePoint Librarian

    […] 12/8/17: There’s a better way to do this using Microsoft Flow, if that’s available to […]

    Reply

Leave a Reply

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

Discover more from Nate Chamberlain

Subscribe now to keep reading and get access to the full archive.

Continue reading

Code of Conduct - Terms and Conditions - Privacy Policy