Time zone setting for enterprise account

Nicolas Michel

Hello Team,

I'm getting really frustrated with how SmartSheet is handling the dates. Everything seems to be the American format by default for any form of notification or automation regardless of your personal or column settings. Why can't the default format be changed?

Example attached. The column format for Start Date and End Date is set-up as DD-MMM-YYYY and my personal setting is Europe, yet the notification is displaying MM-DD-YY

Date format.jpg

It is the same with "Generate a document" and formulas in date column. The system overwrites the column format to be the American format rather than the format of the column in the sheet.

As we are working both in Europe and America, I've set-up every date column in the solution to DD-MMM-YYYY to avoid confusion but to no avail.

This is impacting our ability to automate reporting as at worth the date could be 12 months out on the report if people are inputting 01/12/22 (1st Dec 22 [EU] or 12th Jan 22 [US]) and the report comes at with 12/01/22 (12 Jan 22 [EU] or 1st Dec 22 [US]) or vice-versa.

Any ideas / help would be appreciated. This isn't a new challenge but I'm yet to find a suitable answer.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/361111#Comment_361111

A potential workaround would be to use a formula against the date value to create a date value stored as text in a helper column.

You might try something like using a lookup table on another sheet to relate month numbers to month names. A MonthNum column and a MonthName column would suffice. Then you could do something like this:

=INDEX({Month Lookup Sheet MonthName range}, MATCH(MONTH(DateColumn@row), {Month Lookup Sheet MonthNum range}, 0)) + " " + DAY(DateColumn@row) + ", " + YEAR(DateColumn@row)

So if the value in the DateColumn set for the UK region is 10/2/23, the helper column would show "February 10, 2023". Then you send the helper column value in your automated alert and use the helper column value for your generated document as well.

Stacy Meadows

Try this I had some issues previously :

Bottom left circle - Settings - Time Zone and Regional Preferences

I would advise getting the sheet owner to also do this if it is not yourself :)

Jeff Reisman

@Nicolas Michel

I think the dates in the date fields on the email notification is always going to default to the date format of the column as designated by the sheet owner.

Since you're dealing with two date formats that can get easily mixed up, maybe have the sheet owner/admin change the column date format to something like DD-Mon-YYYY (today is 19-Dec-2022) or Month DD, YYYY (today is December 19, 2022). That way it's impossible to get the day and month mixed up.

Nicolas Michel

@Stacy Meadows @Jeff Reisman

Thanks for your reply. I've already done all of what you suggested which is why I'm at a loss. Every single date column format is DD-MMM-YYYY, My setting and the enterprise account setting is GMT and UK.

It looks like the automation (e-mail, generate document) is ignoring the column date format. I even had an instance with a formula in a date column not displaying the correct date. I'll keep asking SmartSheet to look at it.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/361111#Comment_361111

A potential workaround would be to use a formula against the date value to create a date value stored as text in a helper column.

You might try something like using a lookup table on another sheet to relate month numbers to month names. A MonthNum column and a MonthName column would suffice. Then you could do something like this:

=INDEX({Month Lookup Sheet MonthName range}, MATCH(MONTH(DateColumn@row), {Month Lookup Sheet MonthNum range}, 0)) + " " + DAY(DateColumn@row) + ", " + YEAR(DateColumn@row)

So if the value in the DateColumn set for the UK region is 10/2/23, the helper column would show "February 10, 2023". Then you send the helper column value in your automated alert and use the helper column value for your generated document as well.