How to convert UTC number date ib Survey123 to local format in Power Automate?

402
1
Jump to solution
11-29-2022 06:53 AM
GregKeith
Occasional Contributor III

I'm using Power Automate to send data to an Excel sheet when a survey is submitted (using Watercraft Invasive Mussels template in Web Designer). The field 'Observation Date' is displaying in Excel as 1669654800000 for Nov 28, 2022. 

Based on this post , I entered the formula 

addseconds('1970-1-1', Div(triggerBody()?['feature']?['attributes']?['observation_date'],1000) , 'yyyy-MM-dd')

as a function expression in Power Automate. When I run this, the survey submits correctly to the feature layer, but never appears in Excel. Eventually I get an email telling me one of my fields is Null.

Any clues about how to deal with this? Thanks.

0 Kudos
1 Solution

Accepted Solutions
GregKeith
Occasional Contributor III

Got it working. Added a new date question named newdate and used the addseconds formula above, replacing 'observation_date' with 'newdate'. 

Also shoutout to @IsmaelChivite who spent more than a couple minutes helping me out in a webinar.

Error messages in Power automate leave a lot to be desired. 

View solution in original post

0 Kudos
1 Reply
GregKeith
Occasional Contributor III

Got it working. Added a new date question named newdate and used the addseconds formula above, replacing 'observation_date' with 'newdate'. 

Also shoutout to @IsmaelChivite who spent more than a couple minutes helping me out in a webinar.

Error messages in Power automate leave a lot to be desired. 

0 Kudos