Automated Email is sending in correct time and date

[FABAudienceSM]

Hello,

I am reaching out to see if anyone else has had this problem and knows how to fix this.

We use a form that is to file reports at the end of a shift, which includes the time stamp of when the form is being filled out. This form is then sent out to Administration and Management staff via the Automation tool in Airtable. Which then gets populated into the corresponding Base. Strangely, when the automation sends out the email, it creates a strange and incorrect time signature along with the date (example: Saturday, 2023-09-24T02:26:00.000Z when it was the 23rd of September and at 9:26pm). 

It does not effect the base however, and places the correct date and time in there, but I am wondering if anyone has had this same issue and has found a way to correct it??

[AirOps · Accepted solution]

Hi @FABAudienceSM 

Welcome to the Airtable community! The issue you are facing has todo with the way Airtable deals with dates on the back end of its software. All dates are stored in UTC timezone in ISO formatting. So when you call this date field in an automation or formula you end up with a result that isn't ideal. 

To get around this I suggest using the function DATETIME_FORMAT( ) in a formula field that you can call in your automation rather than your date field. Airtable has a great help article on how to achieve this here.

You will also need to set the date you are using to the correct timezone. You can do this by using the SET_TIMEZONE( ) function, which you can read about here. 

Once implemented your formula will look something like this: 

DATETIME_FORMAT(SET_TIMEZONE([yourDateTimeField] , ['timezone identifier']) ,  '[format specifier]')

I hope this helps! 

Chantal

[FABAudienceSM]

Hi Chantal,

Thank you for the formula information. I just tried this out and made the formula work in the Table itself, but not in the automation email that would be sent out. Is there any reason why it is not populating in the email that would be sent out? This is the formula that I put into the table.

DATETIME_FORMAT(SET_TIMEZONE({Report Date},'America/Chicago') ,'MM-DD-YYYY, LT')

Its showing on the table correctly, but sadly, no in the Automated email, which is sent out after someone has filled out the Form.

[AirOps]

Hi @FABAudienceSM, 

Are you utilizing Airtable automations to send out this email or are you toggling on the "Email me at XYZ email" function at the end of the form view?

If you are using Airtable automations please ensure that when you include the timestamp in your email automation that you are now utilizing the formula field you set up in your table rather than the date field. If you are calling the new field, the formatting should be correct. If you are still running into issues, could you kindly attach an photo of the email set up of your automation so that I can investigate further? 

If you are using the toggle, I would recommend setting up an email automation instead. This way you can format the email with the correctly formatted date. You would use the trigger "When a form is submitted" and the action "Send Email". 

[FABAudienceSM]

The previous solution worked out! (Sorry for the late reply) It just took a new report to go through to show up correctly. Thank you all for your help!