Others

How to Lock a Completion Date in Microsoft Lists Using Power Automate

John Wilson Sep 27, 2026 870 views

Question details

The user needs to permanently record the exact date and time in a Microsoft Lists column when an item's status changes to Completed, without it being overwritten by future updates.

How to Lock a Completion Date in Microsoft Lists Using Power Automate

Product: Microsoft Lists and Power Automate
Device & OS: not provided
Scenario: Setting a permanent completion timestamp on a list item once its progress column is marked as Completed.
Observed behavior: Using a calculated column causes the date to change continuously upon editing, and a basic Power Automate flow using utcNow() overwrites the initial completion date whenever the item is modified again.

Before you start

Verify that your Microsoft List has a choice column named 'Progress' and an empty Date/Time column named 'Completion Date' before creating the automation flow.

Solution 1Recommended

Create a Conditional Power Automate Flow to Lock the Date

Configure a flow that only writes the completion date if the date column is currently blank, preventing subsequent edits from overwriting the original timestamp.

Microsoft Lists does not provide a direct column-locking feature. To achieve a static timestamp, you must use Power Automate to check if the completion date field is empty before applying the utcNow() function. Because the flow will only update the item when the date is blank, any later edits to the list item will safely bypass the timestamp update.

Set up the flow trigger

Open Power Automate, create a new automated cloud flow, and select the SharePoint trigger 'When an item is created or modified'. Connect this trigger to your specific site address and Microsoft List name.

Add the condition block

Click 'New step' and add a 'Condition' control. In the first row, configure the rule to check if the 'Progress Value' is equal to 'Completed'.

Configure the blank date check

Within the same condition block, click 'Add' to insert a new row (ensure the logic is set to AND). Configure this second rule to check if the 'Completion Date' is equal to null (leave the value box entirely empty or use the null expression).

Update the item with the current time

In the 'If yes' branch of your condition, add an 'Update item' SharePoint action. Select your site and list again, map the mandatory fields (like ID and Title), and in the 'Completion Date' field, enter the expression utcNow().

Save and test the workflow

Save your Power Automate flow. Go to your Microsoft List, change an item's progress to 'Completed', and wait for the flow to run. Afterward, edit the item's title or description to ensure the Completion Date remains locked.

Create a Conditional Power Automate Flow to Lock the Date

Flow Logic Confirmation: By verifying the Completion Date is null before updating, you successfully simulate a locked column behavior in Microsoft Lists.

Free Microsoft Office alternative

Manage Data and Track Projects Effortlessly with WPS Office

Microsoft Lists and Power Automate offer robust automation but require building and troubleshooting complex flows. If you are looking for a straightforward, highly compatible alternative for tracking projects and managing tabular data without a steep learning curve, WPS Office is an excellent solution.

1. Download WPS Office: Visit the official WPS website and download the free installation package for your operating system.
2. Install and Launch: Follow the simple installation wizard and open WPS Spreadsheet to start organizing your data.
3. Create a Project Tracker: Set up a new spreadsheet, utilize built-in data validation for dropdown statuses, and track your completion dates manually or via simple macros.

Fully compatible with Microsoft Excel (.xlsx) formats and macrosFree and lightweight office suite for Windows, Mac, and LinuxEasy-to-use spreadsheet interface for logging fixed dates and project trackingFamiliar UI allowing seamless migration from other Office suites

Frequently Asked Questions

Why does a calculated column in Microsoft Lists keep updating the date?

Calculated columns evaluate their formulas (such as TODAY() or NOW()) dynamically every time an item is modified or viewed. They are designed for real-time calculation and cannot natively store a static, one-time timestamp without relying on external automation like Power Automate.

Is there any way to lock a column natively in Microsoft Lists without Power Automate?

No, Microsoft Lists currently lacks a native column-locking feature or a static 'Timestamp upon completion' column. Creating a workflow via Power Automate is the standard and required Microsoft workaround to achieve this behavior.

What happens if a user changes the list item status back to 'In Progress'?

With the recommended flow, the locked date remains unchanged because the workflow only updates the item if the date field is empty. If you want the date to be cleared when the status changes back, you must create an additional conditional branch in Power Automate that clears the Completion Date field when Progress is no longer 'Completed'.

Office Quick Solutions