SharePoint Document Issues

Use a Today Date Alternative in SharePoint Calculated Columns

Anum MushtaqAnum Mushtaq Nov 13, 2026 869 views

Question details

The user needs to compare an expiry date with the current date in a SharePoint calculated column.

Use a Today Date Alternative in SharePoint Calculated Columns
Product
Microsoft SharePoint
Device & OS
not provided
Scenario
A list or document library contains expiry dates that need to be evaluated against today's date.
Observed behavior
The TODAY function cannot be used directly in the calculated column formula.
Before you start

Confirm that the expiry date column is a Date and Time column and that you can create columns and a scheduled Power Automate flow. Identify whether the data is in a SharePoint list or a document library so you can choose the corresponding flow actions.

Solution 1Recommended

Use a date column updated daily by Power Automate

Use this approach when the comparison needs to change as days pass without manually editing every item.

A separate date column is not automatically refreshed just because it is named Today. A scheduled flow must update its stored value so the calculated column can recalculate.

1
Create a reference date column

In the SharePoint list or library, select Add column > Date and time. Name the column TodayDate and choose a date-only format.

2
Create a calculated column

Open Settings > List settings or Library settings, then select Create column and choose Calculated. Enter =IF([ExpiryDate]-[TodayDate]>90,"Yes","No"), replacing ExpiryDate with your actual column name, and select Single line of text as the return type.

3
Create a daily flow

In Power Automate, select Create > Scheduled cloud flow and set a daily recurrence. Add SharePoint Get items for a list or Get files (properties only) for a document library.

4
Update each reference date

Add Apply to each using the results from the previous action. Inside it, add SharePoint Update item for a list or Update file properties for a library, use the current item's ID, and set TodayDate to the expression formatDateTime(utcNow(),'yyyy-MM-dd'). Supply any other required fields.

5
Test the comparison

Run the flow using Test in Power Automate, then check the calculated column in SharePoint. An expiry date more than 90 days after TodayDate should show Yes; a date 90 days or fewer away should show No.

Use a date column updated daily by Power Automate
Timing and coverage: The example uses the UTC date. Adjust the flow for your local time zone if needed, and configure pagination if the list or library is larger than the retrieval limit. Results reflect the last successful update, not a continuously changing clock.
Free Microsoft Office alternative

Compare Expiry Dates in WPS Spreadsheets

For expiry-date tracking outside SharePoint, WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files and use date formulas as you move your work to WPS.

Compare expiry dates with TODAY() in a spreadsheet.Open and edit Microsoft Excel XLSX files.Work in a familiar, easy-to-use spreadsheet interface.Use a lightweight, free Office alternative.
microsoft office alternative - wps office

Frequently Asked Questions

Will naming a SharePoint column Today make it update automatically?

No. A date column stores a value; its name does not make it change daily. Use a scheduled Power Automate flow to update the column for each item.

What does the 90-day formula return?

The formula =IF([ExpiryDate]-[TodayDate]>90,"Yes","No") returns Yes when the expiry date is more than 90 days after the stored reference date. It returns No for 90 days or fewer, including dates in the past.

Why might the result still show yesterday's date?

Check the scheduled flow's run history in Power Automate and confirm that it updated the item. The example uses UTC, so the stored date may also differ from your local date near midnight.

Can I use the same flow for a document library?

Yes. Use SharePoint Get files (properties only) to retrieve library entries and Update file properties inside Apply to each. For a list, use Get items and Update item instead.

What should I check if the IF formula has a syntax error?

In the calculated column settings, verify the column names in square brackets and test a simple date subtraction first. Check the site's regional settings and formula separator requirements before adding nested IF statements.