Using Calculated date Fields in your Search solutions

Case: you have a Content Type for your Policies Pages and want to ensure that they are reviewed every 180 days.

You have at least 3 options:

1) add a "Next review date" DateTime site column to the Content Type and be depended on the users remembering to update the column. You could also set up a workflow that will set the value in the "Next review date" on updates, but that is a lot of work when there are better alternatives.

2) Add a calculated site column that will set a value of Modified + 180 days

3) Use the out of the box "Last Modified Date" columns and an offset to handle the review date. This option will not provide a field/column containing the value, which might be a disadvantage if you are planning to use it in Power BI or similar.

In this blog post I will explore the Calculated Column option.

TLDR: The calculated field is display only, it can't be used as a Refiner.

On the right-hand side is the definition of the new site column.

It is created in the

Content type gallery and added to my existing ContosoPolicyPage content type. It is of type Calculated, and is using the "Modified" column as part of the Formula (Modified+180)

The data type returned is Date and Time, but the time has been excluded.

Next step is to add the Contoso Policy Page content type to Site Pages library in a site collection, and create a few pages

Hit the "Search and offline availability" link in the site settings to trigger the Indexer to reindex the site. This will cause the creation of the ows_ContosoPageNextReviewDate crawled property.

Once this crawled property shows up in the Search Schema admin page (_layouts/15/searchadmin/ta_listcrawledproperties.aspx?level=tenant ) , then you will have to create a new Managed Property (in this case named "ContosoNextReviewDateAsText").

Please note that manually created Managed Properties can only be of type "Text" or "Yes/No"

(warning, read this section before doing it)

Map one of the RefinableDate fields to ows_ContosoPageNextReviewDate

Map ows_ContosoPageNextReviewDate to one of the RefinableStrings

Once the mapping has taken effect (values shows up in the RefinableString property) you can start setting up the Search Page:

As you can see above, the RefinableDate property is empty, and when you see the value in the RefinableString property, it makes sense as this value can't be transformed into a date (datetime;#2026-04-09T07:34:48Z)

Ergo, the calculated field can't be used as Refiner, which severely limits it use in many scenarios.

In general, I will recommend NOT to use a calculated date field in your search solutions. Just use the Created or Modified date field and manage the offset yourself.

In order to making this value into a readable date I used this HandleBars expression:

This can, of cause, be extended with color coding the date based on how many days there are to the review date, as show in my previous post, Formatting your data in PnP Modern Search Handlebars makes the difference