Others

How to Track First and Last SharePoint Item Dates Using Power Automate

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to display the earliest and latest creation or modification times for files within a specific SharePoint folder using custom summary columns.

Product: Power Automate
Device & OS: not provided
Scenario: Monitoring document activity timelines within a SharePoint directory by automatically recording the very first and most recent file updates.
Observed behavior: The goal is to trigger an automated process whenever an item is created or modified, which calculates and updates the folder's summary date columns accordingly.

Before you start

Ensure you have permission to edit the target SharePoint list or library and access to the Power Automate environment to create and test custom cloud flows.

Solution 1Recommended

Create a Custom Power Automate Flow to Calculate Dates

Set up an automated cloud flow that monitors folder activity, retrieves all files, sorts their timestamps, and updates the designated SharePoint columns.

Because SharePoint cannot natively aggregate the earliest and latest dates of files within a folder into a parent column automatically, you must rely on a Power Automate flow. This flow will trigger upon any file change, analyze the folder's contents, and write the calculated minimum and maximum dates back to your list.

Set the SharePoint Trigger

Open Power Automate, create a new 'Automated cloud flow', and select the trigger 'When an item is created or modified'. Connect it to your specific SharePoint Site Address and Library Name.

Retrieve Folder Contents

Add a 'Get files (properties only)' action. In the advanced options, configure the 'Filter Query' using OData to restrict the retrieved files strictly to the target folder you wish to track.

Calculate the First and Last Dates

Initialize variables to process the dates. You can use expressions or sort the returned array of files by their creation/modification dates to easily identify the first (oldest) and last (newest) timestamps.

Update the Summary Columns

Add an 'Update item' (or 'Update file properties') action. Map the calculated earliest date and latest date to your custom SharePoint columns to save the results.

Consult the Power Automate Community

For exact OData filter syntax and complex date comparison expressions tailored to your folder structure, post your specific requirements in the official Microsoft Power Automate Community forum.

Advanced Expressions Required: Comparing dates in Power Automate often requires using functions like ticks() or carefully structuring your arrays to ensure the oldest and newest items are accurately identified.

Free Microsoft Office alternative

Looking for a Simpler Way to Manage Documents?

If you find Microsoft SharePoint and Power Automate workflows too complex for everyday document management, consider switching to WPS Office. It provides a lightweight, highly compatible alternative for handling documents, spreadsheets, and presentations without the need for intricate cloud automations.

1. Download WPS Office: Visit the official WPS website and download the installation package for your operating system.
2. Install and Launch: Follow the on-screen instructions to install the software, then launch WPS Office.
3. Open Your Office Files: Simply drag and drop your existing Microsoft Office documents into the application to continue working instantly.

Seamlessly compatible with Microsoft Word, Excel, and PowerPoint file formats.Built-in cloud storage for easy file access and sharing without complicated setups.Lightweight application that runs fast and smoothly on almost all devices.Free to download with a familiar, user-friendly interface that requires zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Can SharePoint calculate the first and last modified dates without using Power Automate?

No, SharePoint's native calculated columns cannot dynamically aggregate or compare data (like minimum and maximum dates) across multiple items within a folder. A custom script or Power Automate flow is strictly required.

What is the best Power Automate trigger for tracking file updates?

The most reliable trigger for this scenario is 'When a file is created or modified (properties only)' configured for your specific SharePoint library. This ensures the flow runs precisely when a file's timestamp is updated.

How do I filter files by a specific folder in Power Automate?

In the 'Get files (properties only)' action, you can apply an OData Filter Query. Often, users filter using the 'FileDirRef' property to specify the exact folder path and exclude files from other directories.

Do I need a premium Power Automate license for SharePoint connectors?

Standard SharePoint connectors, including triggers and actions needed for this task, are usually included in most Microsoft 365 business and enterprise subscriptions. Premium licenses are typically only needed if you integrate third-party premium connectors.

Office Quick Solutions