Date Fields

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.

Find more posts tagged with

Comments

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!!

Sign in to join the conversation!

Quick Links

Trending discussions

Project Comment/ Update
Hi, Is there a feature in Smartsheet that allows us to add comments daily, weekly, or monthly, and filter them later to review what happened on the project during a specific week or month? or create report ? Thanks JCG
Advise on Workgroups and access for Groups
I have Workspaces that have sheets containing sensitive information. The Workspaces have Individual access to a few people. With in the Workspace, I created Dashboards, with Dynamic Views. Each is set with specific Groups to access specific information. The users in the groups can not see or access the Dashboards, unless I…
Document filtered by form responses
I've created a Smartsheets form. When someone submits the form, I want them to automatically be emailed a document that has resources and links, but the document is filtered to only show the content that corresponds with what they selected in the form. Is something like this possible? What types of documents or automations…