Unanswered

utcnow logs wrong date in sharepoint list

(0) Share

Report

Posted on by Community Power Pla...

I have a flow that triggers when a task is marked as completed in a SP task list and updates date field 'Completion Date' as current date. Expression I am using is: adddays(utcnow(),0,'yyyy-MM-dd'). Today's date is 12/17/2019, when a user marked a task as complete at 7pm today, the completion date got logged into Sharepoint as 12/18/2019 which is weird as couple of tasks marked as completed earlier in the day had completion date tagged as 12/17/2019.

Can someone please help me get this fixed, I am working in eastern time.

Categories:

Building flows

I have the same question (0)

All responses (9)

Answers (1)

Pstork1 68,697 Most Valuable Professional on at

Like (1)

Report

This is a common issue. The problem is caused by a mismatch between SharePoint, which stores time and date in the regional settings for the data center and PowerApps which is going to pick up the user's regional settings. Dates are actually stored as a combination of time and date translated to UTC. So I suspect that something in the combination of the SharePoint timezone, the user timezone and storing things in UTC shifted the time several hours later, to after midnight, and it was recorded as tomorrow.

What are the regional settings for the SharePoint site and the user's location?

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Thanks for the feedback, appreciate it. The regional settings are set to eastern time zone and user location is NYC,US. Thoughts?

Was this reply helpful? Yes No
Pstork1 68,697 Most Valuable Professional on at

Like (0)

Report

Since Eastern time is Five hours behind UTC you are getting the expected date. 7PM Eastern is midnight UTC, which means its the next day in UTC. You can either adjust the time before its tranferred to UTC or make sure to do the conversion back to Eastern when you pull the log.

Was this reply helpful? Yes No
v-bacao-msft on at

Like (0)

Report

Hi @Anonymous ,

You could do the following test to find out the specific time difference.

To create a Date and Time column in the SharePoint list, it needs to include time.

Then add items through Flow. Use the utcNow() function when configuring this field to see the time difference between the time recorded in the SharePoint List and the actual time.

After finding out the specific hours, you can use the addDays() function to increase or decrease the corresponding hours when configuring the Completion Date field again.

Please take a try.

Best Regards,

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Thanks for the feedback, can you please share an example so I am clear on that.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Can you please help me with an example to better understand what you are describing, thanks you.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

@v-bacao-msft I am not concerned with the time at all, I need to make sure that date stamp is returned correctly which is not the case right now, what is the alternative to get current date by eastern time zone? Please advise.

Was this reply helpful? Yes No
Pstork1 68,697 Most Valuable Professional on at

Like (1)

Report
If you need just the date and aren't worried about the time it was completed then I would take the date/time string and strip off the time, then concatenate a time that you know won't cross the timezone line. Here are the formulas you will need. I broke them out into steps, but if you embed each inside the other where the Labelx.Text is in the formula you can do it all in one formula. Its just hard to read. Or you can do it in a series of Compose actions.

formatDateTime(outputs('StartTime'),'MM/dd/yyy hh:mm:ss') First(split(outputs('Compose'),' ')) concat(outputs('Compose_2'),' 12:00 PM')

The first function takes your input time and converts it to a local timestring. Since you are doing this in the flow it should stay in the local timezone. If its not your need to do a Convert to timezone action first.

The second function splits it into an array and takes the first element, which is the date alone.

The third function concatenates Noon as the hours portion to the existing date. Then when it is stored in UTC the date will remain today.

Was this reply helpful? Yes No
Verified answer

Community Power Pla... on at

Like (0)

Report

Thanks @Pstork1 @v-bacao-msft for your inputs, finally below worked for me. It returns date as per eastern time zone and works great. Took Reference from Solution posted here: https://powerusers.microsoft.com/t5/General-Power-Automate/Formatting-Date-And-time-in-MS-Flow/td-p/71597 by @Glenda , thank you.

convertTimeZone(utcnow(),'UTC','Eastern Standard Time','yyyy-MM-dd')

Was this reply helpful? Yes No