Cross Linking Date Cells

Bladerunner

Hi I don't know if this is a bug or not but, I have a summary sheet which has a column set as Text/Number I link into it from a number of sheets, the links can be contact names, descriptions, addresses etc, I have two cells in the column I want to populate with a start and an end date taken from a project plan sheet. When I use the "link from the cell in other sheet" option, I get the date across but it is in the format MM/DD/YY and not DD/MM/YY. Any ideas? I can't set the column to Date as there are text entries as well. My date format is set correctly in personal settings and I am the creator/owner of both sheets.

Kind regards.. Ben.

p_bureau

Hi,

 

I came accross several bugs regarding European settings... SmartSheet seems to be very US focused, I hope they will offer more flexibility very soon!

 

For example, the fact we can't set Monday as the starting day in formulas or in the calendar is pretty upseting...

 

Please let me know if you get any news regarding your issue!

 

Best regards,

 

Paul

Chris McKay

Hello all,

Yes, being a non-US Smartsheet customer, I too have found it very much orientated to US customers. While I understand that, the fact that you can set regional locale information but it almost universally ignored for dates and timezones means it's all but useless. Smartsheet's responses to complaints about the way dates are handled are less than encouraging as they appear to have their own development agenda that does not align with anything their customers seem to be requesting. For a PPM tool, it is really lacking some very fundamental features.

Anyway, I digress. To get around your date issue, I'd suggest one of 2 options:

1. Preferred Option: Create a date column as above and ensure it is set to "Restrict to dates only". When linking to these dates from another sheet, ensure that the target sheet column is a Date format and is also set to "Restrict to dates only".
2. Plan B: Create a date column to the right of your Text/Number column and copy dates from your Project Plan (or link cells). Then, copy the following into the Text/Number column (on the same row):
   
   LEFT([Date Column]#, 8)

These approaches seem to satisfy Smartsheet's desire to switch the dates at random.

Bladerunner

Hi Chris,

 

Thanks yes option 2 works although it's messy. I find I have had to do this in a number of areas, creating hidden columns and then adding formulae. Although it does the job, it concerns me that this makes the sheets harder to support in the future.

Chris McKay

Glad I could help.

Yes the lack of movement on this issue concerns me. I'm actually in the process of looking at whether my firm spends the money to renew our Smartsheet licenses and this (amongst many other things) means the decision is not so straightforward.

It has been raised many times stretching back 2 years:

https://community.smartsheet.com/discussion/bug-comparing-maxsystem-date-system-date-returns-incorrect-result