Problem with date formatting in atomic email

[Jerry_Chan]

When inserting a Date field in an Email Action, the Date and Time format I’ve set within the table does not get used. Instead, I get the default formatting, something like: 2020-06-07T01:00:00.000Z. I’d like the date and time to be outputted in a more readable format in emails, something like 6/7/2020 at 1:00am.

[ScottWorld · Accepted solution]

Welcome to the community, @Jerry_Chan!

As you’ve discovered, the automations won’t pull the date formatting directly from the date field itself.

But there’s an easy fix, and this also gives you a lot more flexibility than what a date field can give you. 🙂

Your solution is to simply create a formula field where you format the date in whatever way you’d like, using the DATETIME_FORMAT function.

So in your case, your formula would look like this:

DATETIME_FORMAT({Your Date Field},'M/D/YYYY \at h:mma')

The only tricky part here is that I had to put the backslash character before the letter “a” in the word “at”, because that “escapes” the character, meaning that it will output exactly as I typed it in.

Otherwise, without the backslash character, that “a” would have turned into “am” or “pm”. And that’s because the letter “a” is one of your formatting specifiers for the DATETIME_FORMAT function.

Once you build your formula, then you can just reference that formula field inside your email action, instead of referencing your original date field.

Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

[kuovonne]

Welcome to the Airtable community!

To add on to ScottWorld’s advice, the date/time will be in GMT time, unless you also set the timezone with the SET_TIMEZONE function.

DATETIME_FORMAT(SET_TIMEZONE({date/time field}, 'Australia/Sydney'), 'M/D/YYYY h:mm')

[ScottWorld]

Thanks for the additional catch, @kuovonne!! :cowboy_hat_face: :raised_hands:

[Owen_Moss]

I had the same issue so I tried the solution in this thread, however when I put the field that has the `DATETIME_FORMAT` function in the email action and generate a preview of the email the field doesn't show at all inside the email (see screenshots).

What am I missing here?

[Melissa_Hanson1]

In order to get the emailed date in the subject line, I created a column just to show today's date. 

DATETIME_FORMAT(TODAY(),'M/D/YYYY')

Now I can reference it in my email trigger and it will show correctly.

[flgmkr]

I'm having the same issue.  Did you find a solution?

The formatted date fields show up fine in the table but are always blank in the generated email.

I'm interested in the time the record was last modified.  I added the "last modified" field to the table but any time I include that field in the email it is blank too.

[flgmkr]

It turns out I had to update both the Trigger and Action Test Steps to see the correctly formatted email in the preview.  I was only updating the Action Test Step.

[chindman]

I did this and it fixed my time formatting in my email automation but it messed up my year. How do I fix this?

See attached, its displaying 9/21/232023

[jachildr]

So this doesn't work for me, because the trigger step is using the filter "[Date field] is xx number of days ago". 

Any other solutions for this? I followed steps above and created a new formula field to format my date in the email, but now the date isn't showing up at all in the email.