Use TODAY() function in SharePoint list

Question

I set the "today" column yesterday (e.g. 10 Sep) and expect it will change to another date automatically (e.g. 11 Sep today). However, nothing has been changed in this column even refresh, then the calculated test cannot be work. Is it normal and expected?

What can I do if I want this column become the date of TODAY to separate the past, today & forthcoming items based on the column of Today or anything else?

Answer 1

Dear User,

Thank you for reaching out regarding the issue with

Based on our researching, the calculated column methods with "Today()", however, are a snapshot of today's date. They will not update automatically or regularly; just when a list item is modified.

If you need a “live” always-updated value regardless of items being modified, here is the workaround you can use to achieve what you're aiming for:

• Instead of relying on TODAY() in a calculated column, you can create a custom Date field (e.g., "Today's Date") and use Power Automate to automatically update this field to the current date every day.
• You can then use this "Today's Date" field in your calculations to determine if an item is past, today, or upcoming.

Summary: The TODAY() function doesn’t automatically refresh, using Power Automate to update a custom "Today's Date" column daily is an effective and reliable solution.

We are committed to ensuring you have a smooth experience with Microsoft 365 products, and we appreciate your patience and understanding as we work through this challenge.

If there is anything further we can assist you with in the meantime, please do not hesitate to reach out.

Best regards,

Answer 2

I created the formula & it works on 3 Oct.

Result looks good on 3 Oct as below per my expectation.

However, today I find the"Period" column will not changed according to the formula after I refreshed the Lists. The Oct 2nd & 3rd items still shown as "Today" unless I click editing the item & save, then it changed to "3.Previous Record".

Seems the formula CANNOT automatically updated even refresh. If I need to click edit items one by one again, it is not an automation with formula.

Any recommendation?

My purpose:

Item with date of Today under the Period of "Today" no matter when the item was created.

Item once in "Forthcoming" will become item under "Today" if the date becomes "Today".

Answer 3

Any solution or automation that updates the Status column (Today, Forthcoming) according to the current date?

Thank you for your help