Using international date format in auto-generated notifications

SSK_REGN

Hello,

I have created a workflow to notify the users that their issue will be resolved on a specific date. That date is stored in the SmartSheet in a column called "Resolve Date" . So, in the message I used {{Resolve Date}} to show this value.

Problem is, it always shows the date in US date format (mm/dd/yy), although I have set the SmartSheet column to display in the International Format (dd-mmm-yy). Since the users of this data/SmartSheet are spread around the world, it would be useful to have International Date format in all such communications. In the personal settings, there is no option to set the default date format to International Date Format.

Is there any way to accomplish this?

Paul Newcome

Ok. SO the first thing we need to do is convert the month number into the month text. The most straightforward (but bulkier) solution is a nested IF.

=IF(MONTH([Date Column]@row) = 1, "Jan", IF(MONTH([Date Column]@row = 2, "Feb", ....................................................................................................................................................................................................................., "Dec")))))))))))

Now we throw the day and a dash in front of it:

=DAY([Date Column]@row) + "-" + nested_IF

Then we throw another dash followed by the two digit year on the end:

=DAY([Date Column]@row) + "-" + nested_IF + "-" + RIGHT(YEAR([Date Column]@row), 2)

And that should do it for you.

Paul Newcome

You would need to insert a text/number column and use a formula to convert the date into this format. Then you can use the placeholder on this new column.

SSK_REGN

Thanks. Is there a direct function that can convert a date into International Format?

Paul Newcome

There is not a direct function. I would be happy to help with a formula if you could please explain the "mmm" portion. What exactly goes there? A number or text?

SSK_REGN

Thanks! yes, we would like the date to look like '27-Feb-2021'

Paul Newcome

Ok. SO the first thing we need to do is convert the month number into the month text. The most straightforward (but bulkier) solution is a nested IF.

=IF(MONTH([Date Column]@row) = 1, "Jan", IF(MONTH([Date Column]@row = 2, "Feb", ....................................................................................................................................................................................................................., "Dec")))))))))))

Now we throw the day and a dash in front of it:

=DAY([Date Column]@row) + "-" + nested_IF

Then we throw another dash followed by the two digit year on the end:

=DAY([Date Column]@row) + "-" + nested_IF + "-" + RIGHT(YEAR([Date Column]@row), 2)

And that should do it for you.

SSK_REGN

Yes, thank you so much!!

Paul Newcome

Happy to help. 👍️