Adding time stamp when choice field is set to completed

[AB21]

Hi all,

I have a SharePoint Online List.  One of the columns in the list is a Choice field titled "Status" and another text column titled "Time Completed".  When an item is changed from unassigned or assigned to Completed I would like a flow to run and place a timestamp in "Time Completed" column. 

Please help!

[abm]

Hi @AB21 

Follow the below steps:

Under the trigger (first step) set the below trigger condition

@and(equals(triggerOutputs()?['body/Status/Value'],'Completed'),empty(triggerOutputs()?['body/Time_x0020_Completed']))

This will trigger only when status is completed and Time completed is empty.

Thanks

[AB21]

Thank you!

How do I change the date to DD/MM/YYYY rather than MM/DD/YYYY

[AB21]

Also I dont want live time I want time to stay as it identifies when the user has completed the ticket? How do I do this

Thanks in advance

[abm]

Hi @AB21 

When you update the date field it should be in yyyy-MM-dd format or MM-dd-yyyyy.  Where you want to see MM/dd/yyyy? Is that in your List items user interface?

'Also I dont want live time I want time to stay as it identifies when the user has completed the ticket? ' Not sure what you meant by. What I did is when the status is completed I used utcNow() which is todays date to set. If not explain a bit more about what date you want to update here?

[AB21]

Hi @abm 

The date is currently MM/DD/YYYY and I would like it as DD/MM/YYYY in the sharepoint list 

Also regarding the time part, this keeps updating by this I mean the time in the screenshot of the Sharepoint list under Time completed I have highlighted via a black square is changing time every few minutes. The flow seems to be running and updating the time each run. I noticed in the flow there is a warning about looping is this causing the time to update   

any ideas? 

[abm]

Hi @AB21 

Did you set the trigger condition?

I tested this and it should only trigger once. Its stops for the second time due to Time Completed is not null.

@and(equals(triggerOutputs()?['body/Status/Value'],'Completed'),empty(triggerOutputs()?['body/Time_x0020_Completed']))

[ScottShearer]

@AB21 

As @abm has said, SharePoint stores date internall in the yyyy-MM-dd format.  The format that you see is controlled by your site and column settings.  When doing anything in a Flow with a SharePoint date, it must be in the yyyy-MM-dd format.

If you don't want to see the time in your list, go to the list settings and then to the settings for you date column.  Change to show date only.

[AB21]

Hi @abm @ScottShearer 

The time is still updating once the item has been set to completed, even though the time completed column contains the time already: 

 

Is there a way to stop the time / date updating once it has been added to the item ? so only add if "time completed" column is null

[abm]

Could you show your column 'Date Completed' schema name. Go to the list settings and click the column name. Check the url and at the end of the url it should say field=

Thanks

[AB21]

Any ideas? 

[abm · Accepted solution]

Hi @AB21 

So in your case the schema name is TimeCompleted as one word. Use the below trigger settings. That will do the job.

@and(equals(triggerOutputs()?['body/Status/Value'],'Completed'),empty(triggerOutputs()?['body/TimeCompleted']))

[AB21]

That worked!! Thank you so much!

[AB21]

Hi @abm 

Say if I wanted another Column called Time Assigned and only wanted the time the ticket was assigned to a member of the help desk what would I need to changed? 

[abm]

Hi @AB21 

To get the current time use utcNow() for todays datetime and use formatDateTime() expression.

formatDateTime(utcNow(), 'HH:mm')

This will give you the current time without the date.

Thanks

Thanks

[AB21]

Hi @abm,

Was wondering if you could help! Now the time has changed an hour, how do I reflect this to function to display the correct time now? (1 hour forward)

[abm]

Hi @AB21 

Check your SharePoint Datetime settings.

Thanks

[AB21]

Hi @abm 

The Time Completed is a text column:

 

[abm]

Hi @AB21 

Check this

SharePoint Online: How to Change Date Format to "DD/MM/YYYY"? - SharePoint Diary

Make sure you are set to the right zone or not?

Thanks