Date format in automated emails

Colin_G

Hi there,

I've set up multiple automated emails as part of the workflow process, and have just noticed that the dates contained in the automated emails are in the incorrect format, despite me setting my regional preference in the settings.

I have the dates set up in my settings for the United Kingdom format (i.e. DD/MM/YY) and this is how it shows in the Smartsheet itself. However, I have chosen for the date columns to appear in the automated email (under the personalised message where you can choose which columns to show) and these show up as MM/DD/YY.

I've looked and can't see any way to amend this, which seems odd. Could someone advise if I'm doing something wrong, and if not, could I ask that this is something that can be looked at?

Thanks

Colin

Andrée Starå

Hi Colin,

Have you changed the settings for the date column(s) as well?

More info: https://help.smartsheet.com/articles/2479481-apply-standardized-date-formats-in-your-sheet

Did that work?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Colin_G

Hi Andree,

The date columns all use the regional default format, so are all set up correctly. They show correctly on the sheet, the settings seem to be set up correctly - it's just on the automated emails that still shows the wrong format.

Any guidance/advice you can offer will be gratefully received.

Many thanks

Colin

Andrée Starå

Happy to help!

Have you also checked your personal settings?

Was it correct?

 

Alicia_PRB

Hi Colin_G, did you get a resolution for this issue? I am having the exact same issue here in Australia.

Colin_G

Hi Alicia, forgive me - a lot has happened inbetween me posting the question and now, so I don't quite recall! I don't believe I did get a resolution or managed to make it work (I'm pretty certain that I was doing everything correctly), and have just checked some of the processes/workflows we've used in the past few years and can't see any references to dates, so it's possible we just went about things a different way and removed dates altogether from the emails. I would hope there was some progress in this in the last few years but it sounds like that isn't the case. Apologies I can't give you better news!

Koen H.

Hello,

Is there a solution for this by now?

We are encountering the same problem.

Personal settings the of the people are correct. it's only the email where it's wrong.

thanks!

Daniel Miller

We would like to expand automations outside our firm. Long Date Format is more formal.

We can’t move forward without a resolution.

SoS | Dan Palenchar

Hello everyone,

I've developed a solution for this, find it in this published Sheet.

Summary

• Use a formula in a Text/Number column that turns a Date (in a Date column) into the format you want.
• Add backend columns for whatever values you need (i.e., Day, Month, Year as text, number, etc.).
• Combine the backend values via the formula and then reference this in automations.

Below is how the automation appears. Note that Date is a standard Date column and Date Output is the formula version:

Explanation

To implement this solution you will need to write a formula that looks at a Date column (either directly or indirectly via backend columns) and outputs the format you want. I chose to do this with backend columns for better organization and visualization for this audience.

I added the following backend columns w/ these formulas. Note that I am going to write [Date] in my formulas to denote whatever Date column you use (mine as you can see is literally called Date (This is a DATE column):

1. Day of Month =MONTH[Date]@row > outputs # corresponding to the day of the month
2. Weekday (Text) =IF(WEEKDAY([Date (This is a DATE column)]@row) = 1, "Sun.", IF(WEEKDAY([Date (This is a DATE column)]@row) = 2, "Mon.", IF(WEEKDAY([Date (This is a DATE column)]@row) = 3, "Tue.", IF(WEEKDAY([Date (This is a DATE column)]@row) = 4, "Wed.", IF(WEEKDAY([Date (This is a DATE column)]@row) = 5, "Thu.", IF(WEEKDAY([Date (This is a DATE column)]@row) = 6, "Fri.", IF(WEEKDAY([Date (This is a DATE column)]@row) = 7, "Sun."))))))) > Outputs an abbreviated text value corresponding to the day of the week (e.g., "Mon.")
3. Month (Text) =IF(MONTH([Date (This is a DATE column)]@row) = 1, "Jan.", IF(MONTH([Date (This is a DATE column)]@row) = 2, "Feb.", IF(MONTH([Date (This is a DATE column)]@row) = 3, "Mar.", IF(MONTH([Date (This is a DATE column)]@row) = 4, "Apr.", IF(MONTH([Date (This is a DATE column)]@row) = 5, "May.", IF(MONTH([Date (This is a DATE column)]@row) = 6, "Jun.", IF(MONTH([Date (This is a DATE column)]@row) = 7, "Jul.", IF(MONTH([Date (This is a DATE column)]@row) = 8, "Aug.", IF(MONTH([Date (This is a DATE column)]@row) = 9, "Sep.", IF(MONTH([Date (This is a DATE column)]@row) = 10, "Oct.", IF(MONTH([Date (This is a DATE column)]@row) = 11, "Nov.", IF(MONTH([Date (This is a DATE column)]@row) = 12, "Dec.")))))))))))) > Outputs an abbreviated text value corresponding to the month (e.g., "Jan.")
4. Year (YYYY) =YEAR([Date]@row) > Outputs 4 digit year

With these backend columns you can now get a variety of data formula by using a formula that puts them together whichever ones you like delineated by spaces, dashes, commas, etc.

I made the following date outputs with these formulas in the Date Output column:

• Tue., Jun. 25, 2024 =[Weekday (Text)]@row + ", " + [Month (Text)]@row + " " + [Day of Month]@row + ", " + [Year (YYYY)]@row
• Jun. 25 =[Month (Text)]@row + " " + [Day of Month]@row
• 25 Jun. =[Day of Month]@row + " " + [Month (Text)]@row

Here is the automation I built to show how the formatting goes through. I am showing both Date and Date Output for comparison.

Here is how it looks in SS Automation:

And in GMail:

Hopefully this helps provide the strategy and framework you need to get the exact format you want.

CC: @Daniel Miller @Koen H. @Colin_G @Alicia_PRB