Forum Discussion
How to implement a calculated column using 'Created' column and 'Status Update' column
I am attempting to add a calculated column that will calculate the difference in time between when a form is submitted (shown in a column titled 'created') and when a column titled 'status' is marked as 'done'.
RosieCole A calculated column can't identify when a specific column was changed and use that in a calculated column. You only have the the Modified column but the calculated column would be updated when any column in your list was changed. So you will need another column for the date the status was changed to done and either populate this manually or via a flow in Power Automate that checks if it is the status column that has changed and if the status column is now "Done" and puts the date in. A DateDif in the calculated column could then work out the difference.
Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)Rob_Elliott
To clarify -
I need to, 1. create a column specifically for the date 'completed'.(The column can be updated manually or automatically with Power Automate by setting up a flow that checks if the 'status' column has changed to "Done" and records the date of this change.)
2. Create a calculated column and use a DateDif function to determine the time difference based on this specific date.
Should anything relative to the calculated column be added to the power automate?RosieCole So in this example there is a status choice column called Status and a date time column called Done and a calculated column called DateDif. Before I built the flow I changed the Status column of Antigua to Done then manually added today's date to the Done column. The calculated column automatically added the days between Created and Done using the function:
=IF(ISBLANK(Done),"",DATEDIF(Created,Done,"d"))I then built the following flow and I've used the classic designer in Power Automate as I still prefer it!
It has a condition that if Column Changed: Status is equal to true AND Status Value is equal to done. (It's Status Value because the column is a choice column). If it doesn't meet the condition then nothing happens - the red if no channel.
However if the condition evaluates to true then update the Done column with today's date, utcNow().
I changed the Barbados column to Done. The flow ran and it added today's date into the Done column. The DateDif calculated column updated at the same time. It can take a minute or so for the flow to run but once it has run the DateDif column updates. You do not (and can't) make any reference in the flow to the calculated column.
Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)
