Help

Format Dates for Email Action

Topic Labels: Automations
Solved
Jump to Solution
8319 11
cancel
Showing results for 
Search instead for 
Did you mean: 
Jerry_Chan
4 - Data Explorer
4 - Data Explorer

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.

1 Solution

Accepted Solutions
ScottWorld
18 - Pluto
18 - Pluto

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. :slightly_smiling_face:

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.

See Solution in Thread

11 Replies 11
ScottWorld
18 - Pluto
18 - Pluto

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. :slightly_smiling_face:

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.

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')

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

Owen_Moss
5 - Automation Enthusiast
5 - Automation Enthusiast

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?

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
4 - Data Explorer
4 - Data Explorer

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.

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.

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

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.

OMGosh, so simple but it worked. Thanks for coming back and updating with the fix.

ni5ni6
5 - Automation Enthusiast
5 - Automation Enthusiast

While this fix is indeed easy, can someone explain why do we need to do it this way?
I would expect that FORMATTING a field in data model will apply the same format everywhere, so would like to work it that way, without adding formulas to format it