Survey123 to Excel - Date/time format showing as pound sign

(0) Share

Report

Posted on by arcgis_epro

Hi there Automate community,

I am a new Automate user.

I have a a flow set up that goes as follows:

submit new Survey123 record
write into excel spreadsheet in Sharepoint

I've gotten this to work except for the fields that have date/time in them.

It is appearing in the excel spreadsheet as ##### and keeps adding in #s if I expand the column.

I have the DateTime Format in the Excel spreadsheet set to the below via Power Automate.

Do I need to number format the columns directly in the excel spreadsheet? Do I need to do something on the Survey123 end? Do I fix it in Automate?

My ideal date time format is DD/MM/YYYY HH:SS.

Thanks in advance for your help.

I have the same question (0)

All responses (7)

Answers (1)

v-wenjuan-msft Microsoft Employee on at

Like (0)

Report
Re: Survey123 to Excel - Date/time format showing as pound sign

Hi @arcgis_epro ,

Would you like to add datetime like this to Excel?
Try this function in your targe field:

formatDateTime(utcNow(),'dd-MM-yyyy HH:ss')

Community Support Team _ Wenjuan Zou
If this post helps, then please consider Accept it as the solution to help the other members find it.

Was this reply helpful? Yes No
arcgis_epro 21 on at

Like (0)

Report

Re: Survey123 to Excel - Date/time format showing as pound sign

Hi @v-wenjuan-msft , thanks for your reply! Could you include some screenshots of how to do this? I haven't used a function like this in excel before. Do I start through the Formulas or Data tab? Do I make this change in Excel or in Automate?

Was this reply helpful? Yes No
v-wenjuan-msft Microsoft Employee on at

Like (0)

Report
Re: Survey123 to Excel - Date/time format showing as pound sign

Hi @arcgis_epro ,

Just paste the function here:

The timestamp is UTC time, if you would like to convert it into your regional time zone, use this one (take UTC+8 as an example):
formatDateTime(addHours(utcNow(),8),'dd-MM-yyyy HH:ss')

Community Support Team _ Wenjuan Zou
If this post helps, then please consider Accept it as the solution to help the other members find it.

Was this reply helpful? Yes No
arcgis_epro 21 on at

Like (0)

Report

Re: Survey123 to Excel - Date/time format showing as pound sign

Thanks @v-wenjuan-msft ! That works for the date created field.
I also have another field that has a date the user picks in Survey123. How do I format it using your function and the dynamic content?

I tried this, but it failed.
formatDateTime(addHours(utc(triggerOutputs()?['body/feature/attributes/Contact_date']),12),'dd/MM/yyyy HH:mm')

Was this reply helpful? Yes No
v-wenjuan-msft Microsoft Employee on at

Like (0)

Report

Re: Survey123 to Excel - Date/time format showing as pound sign

Hi @arcgis_epro ,

Change your function to below one, it should work then:
formatDateTime(addHours(utcNow(triggerOutputs()?['body/feature/attributes/Contact_date']),12),'dd/MM/yyyy HH:mm')

Community Support Team _ Wenjuan Zou
If this post helps, then please consider Accept it as the solution to help the other members find it.

Was this reply helpful? Yes No
arcgis_epro 21 on at

Like (0)

Report

Re: Survey123 to Excel - Date/time format showing as pound sign

Hi @v-wenjuan-msft . Thanks for your reply. I think we're almost there. I used the function you suppplied above.. I've gotten this error now.

Unable to process template language expressions in action 'Add_a_row_into_a_table_-_Consult_on_effects_Contractor' inputs at line '0' and column '0': 'The template language function 'utcNow' expects its parameter to be of type string. The provided value is of type 'Integer'. Please see https://aka.ms/logicexpressions#utcnow for usage details.'.

I've double checked the two dynamic content fields I'm working with. They are both originally date fields in Survey123/ArcGIS online, but I think that shouldn't matter since it is a part of a function now?

I did go to the link it sends you to, but wasn't sure what to do with that information.

This is how I have it added in via Automate.

Was this reply helpful? Yes No
Verified answer

arcgis_epro 21 on at

Like (0)

Report

Re: Survey123 to Excel - Date/time format showing as pound sign

I was able to get support from another ArcGIS Online/Survey123 user.

This is the solution that worked for me in the end -
addseconds('1970-1-1', Div(triggerOutputs()?['body/feature/attributes/TargetDates'],1000) , 'yyyy-MM-dd').

Thank you for your patience and help @v-wenjuan-msft .

Was this reply helpful? Yes No