Power Automate Export Survey Excel dateTime field

[ModernElectric]

I have been working on a flow in Microsoft Power Automate to export a submitted form to a Microsoft Excel file. The flow appears to work. However, the dateTime field type from the Survey123 Connection XLX does not export out from the form to the excel file properly. Instead of the data and time, it is just one long number. Even formatting the field type in excel, it doesn't update.

Any suggestions?

[RobertAnderson3]

Dates and times are stored in ArcGIS Online as Epoch time, which is milliseconds from 1970-01-01

So in a flow I use the Add to time action and then a function of:

addSeconds('1970-1-1',Div(triggerBody()?['feature']?['attributes']?['startdatetime'] ,1000))

This puts the time into a format that works, with the Interval set to 0 and the Time Unit to Hour (though I will admit I'm less certain how these two settings on the action work).

I then use a Convert time zone action to format the time into the right time zone for my area and the right format for the date style I want it displayed as.

[ModernElectric]

Thank you @RobertAnderson3 I will experiment with my flow I am trying to build to see if I can get it to work. Anything to do with a Date/Time field with Survey123 and integrating it with any other program is really really frustrating. Not sure if anyone else is having a hard time with it or if it is just me. 

Results:

Is my function correct? 

addSeconds('1970-1-1',Div(triggerBody()?['feature']?['TIME CALLED']?['MM-dd-yyyy'] ,1000))

[abureaux]

@RobertAnderson3Has the correct answer here.

I will just add some personal experience: I am moving away from this solution personally. Reason being that I've had far too many flows fail due to silly little things that cause this math to break. I've found adding a calculate in the S123 form to format the dateTime question to the format Microsoft is looking for is a much easier and more reliable solution.

[RobertAnderson3]

This is probably one of the most inconsistent things in all of my surveys and flows, I have handled dates and times so many different ways over the years, it's always been a whole mess to sort out. I have some using that formatting in the survey itself as well, but I can't say I've run into issues breaking my flows when I'm not using that so maybe I've just been lucky.

What kind of issues have they been @abureaux ?

[abureaux]

Random empty fields (which were marked as required), or mismatched data types (e.g., text in integer field). Both are very rare, but have happened in the past.

I also manage to loose that calculation and have to hunt around for it from time to time, which is annoying.

All in all, I found simply converting S123's dateTime within the app to a "human friendly" format worked much smoother for dumping into SharePoint.

[RobertAnderson3]

It 100% is very frustrating, though I don't think it's exclusive to Survey123 in the tech world haha, dates and times always drive me nuts.

triggerBody()?['feature']?['attributes']?['startdatetime'] is the dynamic content value from the trigger in my flow, which is when a survey is submitted. Make sure this matches where you should get your dynamic content from when you make the function.