Some of the date formats change in my email generated with automation

[swtani]

I have two dates formatted the same way and displaying in the table consistently. When I add them to my email, I am getting two different formats. The data format in the table is US. One appears correctly (5/3/2024) and the other appears incorrectly (2024-05-17).

I also have a date time field that is also US format with time 12 hrs. The data in that field shows up as 5/2/24 5:00pm in the table but appears in the email incorrectly (2024-05-03T00:00:00.000Z).

The only additional piece of info is the dates are getting pulled in from another table with the key being the first date (5/3/24). All of them look right in both the original table and the linked table. I tried changing the two that were showing up wrong to Friendly to see if they would change but they didn't. 

[TheTimeSavingCo]

Yeah displaying date values is pretty tricky when adding them to emails.  I usually end up creating a formula field with DATETIME_FORMAT to output the date exactly the way I want it to show in the email and use that in the automation instead

[swtani]

Yeah I ended up creating formulas and got the strangest results. One of the date formula shows up as defined and the other one does not show up at all. I created an email to show what happened. I am completely stumped. 

[TheTimeSavingCo]

Hm, could you try activating the trigger again so that the test data includes the new data from the formula field? 

[swtani]

Oh yes, I tried about 10 times 😓. The crazy thing is I have two date fields that I created formulas for and they both look the same in the table (correct) and one shows up right and the other is blank. So multiple times, I duplicated the one that worked and just changed the field name using the exact same formula and no luck. I am super stumped. 

[swtani]

Yep, activated it so many times until I had to walk away and take some deep breaths. 

[TheTimeSavingCo]

Oof, sorry to hear that.  If you DM me a read only invite link to the base I'd be interested to take a look at this too.  You mentioned one shows up right and the other is blank and I assume that's in reference to two specific records?  And if so, if you could point those out to me within the linked base that'd be helpful!

[swtani]

It is actually two fields defined the same in a single record. Thank you for your offer to take a look. Since I am not a staff member at the non-profit I am helping, I will need to check to see if this is okay with them. I also created a support ticket. Thank you so much and I will let you know what I hear from them. 

[swtani]

So my colleague told me she usually uses ISO date format, not US. I changed the dates to ISO and they work perfectly in the email automation. There appears to be a bug using US format, even when you try to display a formula that is based on a US date field. I let support know and I now will stick to ISO date formats. Thank you for your replies. It is a comfort to have this community.