I am working on a Survey123 custom report that uses the default {CreationDate} field. When the CreationDate field is used in the report it automatically adjusts the time. For example, In Survey123 the submitted time is 13:09 but when the {CreationDate} field is used in a report, the time is adjusted to 5:09 PM. Basically it changes it to a 12-hour format and adds 4 hours. Do I need to adjust the time in Survey123 Connect prior to the report? or can I use an expression in the report?
Solved! Go to Solution.
I don't want to take credit for this solution so here is the link to the original solution.
Here is my solution based on the US Eastern time zone.
1) In Survey123 Connect XLS add a text utcOffset field. Then insert the following into calculation field ---> if(pulldata("@property", 'utcOffset')='-4','-04',if(pulldata("@property", 'utcOffset')='-5','-05',pulldata("@property", 'utcOffset'))). Use "hidden" In the Appearance column.
2) In MS Power Automate under the Create Report step, place the newly created utcOffset feature attribute in the UTC offset field.
This is likely converting to UTC. Feature Reports tend to do that to date fields that include time (but NOT to time only fields, as they are technically just "text").
I have a field in all my forms to pull the UTC offset:
Then use that in the Feature Report:
${if asb_utc==”-3.5”}${asb_sample_start_time | utcOffset:”-0330”}${/}${if asb_utc==”-4”}${asb_sample_start_time | utcOffset:”-0400”}${/}${if asb_utc==”-5”}${asb_sample_start_time | utcOffset:”-0500”}${/}${if asb_utc==”-6”}${asb_sample_start_time | utcOffset:”-0600”}${/}${if asb_utc==”-7”}${asb_sample_start_time | utcOffset:”-0700”}${/}${if asb_utc==”-8”}${asb_sample_start_time | utcOffset:”-0800”}${/}
Essentially, you need to account for the time zones that you may cross (for me, North America). Basically, it looks like this:
${if asb_utc==”-3.5”}${asb_sample_start_time | utcOffset:”-0330”}${/}
asb_utc = A calculate in your survey, similar to what I show above.
-3.5 = A UTC offset, specific to you (-3.5 is Newfound Land)
asb_sample_start_time = A Date or DateTime question. For you, likely Createdate
-0330 = The correction to apply to your Date or DateTime question.
I don't want to take credit for this solution so here is the link to the original solution.
Here is my solution based on the US Eastern time zone.
1) In Survey123 Connect XLS add a text utcOffset field. Then insert the following into calculation field ---> if(pulldata("@property", 'utcOffset')='-4','-04',if(pulldata("@property", 'utcOffset')='-5','-05',pulldata("@property", 'utcOffset'))). Use "hidden" In the Appearance column.
2) In MS Power Automate under the Create Report step, place the newly created utcOffset feature attribute in the UTC offset field.