Date Fields

Victor Bennett

As the owner of a Smartsheet spreadsheet, I have set my regional preference to English (United Kingdom) so the date is dd/mm/yy

I have Admin that update a date field where the column property is set to 'Date' and restricted to 'date only'.  This column is locked to Admin.

We have a number of Editors (in the USA) who are reporting they are seeing the date field in USA format (mm/dd/yy)

In order to ensure this is input correctly, we are selecting the date from the calendar rather than typing the date manually.

The issue seems to persist on some rows, but not all.  Is there any other way to lock this down to ensure every user uses and sees dd/mm/yy, regardless of their own regional preferences.

The Editors I refer to are Smartsheet customers in the USA so they will obviously have their own regional preferences, but I need to ensure their preferences is not overriding the preferences of the sheet they are invited to use.

J. Craig Williams

If a user using US preferences enters a date, it will be entered as they see it,

for example Oct 9th is 10/9.

When user using UK preferences views this sheet, they will see the date as Oct 9th -- 09/10

The date is stored differently than it is displayed. The only confusion that might occur would be if you are using exports to PDF or some other reporting tool like a dashboard at the same time.

The preferences are by user, not by sheet. 

I have lots of experience in this having worked with non-US engineers and believe there is more problems forcing a user to view the date format in a style they are not comfortable with.

My suggestion would be create another column to capture the date in ISO-8601 format (YYYY-MM-DD) (formula, locked) and let the user enter and view the dates in their preference dictated format.

This formula will convert the date to ISO (in a Text/Number field)

=YEAR([date-col-name]@row)+IF(MONTH([date-col-name]@row)<10,"-0","-")+MONTH([date-col-name]@row)+IF(DAY([date-col-name]@row)<10,"-0","-")+DAY([date-col-name]@row)

Craig

Victor Bennett

Hi Craig,

Thank you for your reply.  I have to admit I am completely useless at formulas and cant get this to work.

The date entered in USA or UK format is in a column called Date Part Fitted.

To the left of this column I have an empty column, for arguments sake, called Computed Date.

Am I to copy and paste your formula into this cell, and replace replace every instance in the formula of [date-col-name]@row to read Date-Part-Fitted@598

(598 happens to be the last row with entered data)

I have uploaded a snapshot of the screen

I basically want the user to enter the date in column Date Part Fitted and the sheet populate the blank cell to the left with the ISO date.

Sorry to be a complete pain!!