Date off by one day when using pre-filled app

[SaS_AT]

Hello - I am using the amazing pre-filled form extension by @kuovonne to pull form data entered into one table that is then updated with a pre-filled form.

The only issue I am having is the date field from the original entry is moved back by one day when the pre-filled form is created (and then updates a new record with the wrong date).

I have tried looking at the time zone settings, but have had no luck. Is there any thought about where the fix might live?

Thank you!

[Summer_Schedule]

I have had the same issue. @kuovonne do you have any insight about this? Help please!

[kuovonne · Accepted solution]

This is often a timezone issue.

If this is a date only field (with no time), try prefilling with DATESTR({date field}) instead of just the date field.

[Summer_Schedule]

Ah, yes, this did help! But, we are using the pre-filled form to allow users to come back and edit their previous submission, so, initially the date fields are all blank, and some may remain blank. Adding DATESTR({date field}) did not work when the date field was blank - I had to add:

IF({date field}, DATESTR({date field}))

I had noticed, though, that when I used the same field in an automated email, clicking on the link in the email showed the correct dates in the pre-filled form, while using the link out of the table subtracted a day. Either way, the dates in the URL did not match the dates showing on the form. For example:

• I entered 10/31/22 on the form and hit submit.
• I then used the link from the table to open the form. The date in the URL was 10/31/22, but the date showing on the form was 10/30/22.
• I then used the link from the automated email. The date in the URL this time was 11/1/22, and the date on the form showed 10/31/22. This is the result I wanted, but how did the URL add a day to the date through the email automation process?

Also, in the automated emails showing the list view of all the responses, the dates show up with a time zone which I cannot change. I don’t even want it to show, but if it had to show I’d at least like it to be correct. Do you know any way around this?

Thank you!

[SaS_AT]

Thanks, @kuovonne! That worked for me!

[kuovonne]

@Summer_Schedules There are many moving parts to your system, including the form itself, the prefill formula, the automation email, the timezone of the field, the timezone of the computer where you are viewing the form, and the actual time. If you have a date/time field versus a date-only field, you have even more complications. When you adjust one aspect it often has slight ramifications on the other parts of the system. And timezones are notoriously difficult to deal with.

Here are some general guidelines:

• When working with formula fields, you can use DATESTR() or DATETIME_FORMAT() with SET_TIMEZONE() to turn the date or date/time into a text string, which can sometimes be more predictable than including the date field directly.
• You can now configure date/time fields to have a specific timezone (but not date-only fields)
• Date only fields are stored with a time of UTC/GMT midnight. This can be interpreted as a different day, depending on how you are accessing the system.