Differing time zones showing on linked cells

PM_Reeves
PM_Reeves ✭✭✭✭
in Smartsheet Basics

Hi,

I have a strange issue whereby I have a cell from a Project Plan leading to another sheet.

The formula in the parent cell is =Modified13 + "" where 13 refers to the line above, and this is how it displays for me, in the UK date format.

Dates 3.PNG

This cell with the update time is linked into another sheet, yet this displays in US format, even though other rows display in UK format. It's the cell with content in the cell to the left. And it takes US Pacific Time.

Dates 1.PNG


My overriding Timezone is set to Adelaide, with Regional Preferences set to English (Australia)

Dates 2.PNG

Can anyone explain what's going on, and any possible method of getting consistent date formats?


Thanks

· Share on FacebookShare on Twitter

Answers

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭

    Hi @PM_Reeves

    Does your problem persist?

    I tried to recreate your problem, but turning to US Pacific time was a momentary phenomenon.

    System time columns display the user time zone value, but when used in a formula, it gets the GMT or UTC. So, since we use a formula =[Modified]13+"", I thought it got converted to GMT value. (In Japan, GMT +9, 10:00 AM or 10:00 becomes 1:00 AM or 1:00 when used in a formula.)

    But, at least in my case, the value consistently showed the time and time format according to time zone and regional preference setting.


    Screenshot 2023-12-01 23.42.06.png

    This is the source sheet with the same formula.






    image.png

    The Text/Number column in the target sheet has the same formula, except line 14 is overridden with an inbound cell link.

    The time zone of both sheets is JST, GMT +9.



    image.png

    Cell history shows that line 13 Modified was 11:36 PM before the cell link.

    At 11:38 PM, when I cell linked to Line 14, column Text/Number, it got 6:38 AM for an instant. Then, the value turned to 11:29 PM, the same as the source sheet value.

    The 6:38 AM is the US Pacific Time.

    Then, I converted my language setting to Japanese from US English.

    image.png

    Then, the linked-in cell had a different format (US format), but the other cell became a 24:00 format.






    image.png

    Then, after the refresh, all the cells, including the linked-in one, got the updated 24:00 format. (another cell without a cell link has now updated values.)





    image.png

    This is the cell history on the target sheet.







    image.png

    This is the cell history on the source sheet. (link out sheet)








    image.png

    Then, this is the cell history after I set the language back to US English.

    · Share on FacebookShare on Twitter
  • PM_Reeves
    PM_Reeves ✭✭✭✭

    Hi @jmyzk_cloudsmart_jp

    Wow! You've done a lot of work there.

    I still had the issue this morning, BUT, I think I've found the solution.

    After accessing one of the projects that was displaying Pacific Time, I looked at cell history. Didn't change anything, but when I exited and went back to the target sheet, the time had changed to Australian format.

    70-odd more to go to prove my theory.

    Thanks for the time and effort you put in.

    Have a great day.

    · Share on FacebookShare on Twitter
  • PM_Reeves
    PM_Reeves ✭✭✭✭

    But, having typed out the above this morning, and updated all sheets to ensure consistent date formatting, after entering some project updates this afternoon, the issue has returned.

    Going to leave it to see if anything changes overnight and Smartsheet catches up with itself.

    · Share on FacebookShare on Twitter
  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭

    Hi @PM_Reeves

    A new function, TIME(), may help you.

    https://help.smartsheet.com/function/time
    1. Convert the System Time column to Text/Number by adding "".
    2. With the text function like the RIGHT() function, get the Time part text, "5:10 PM", for example.
    3. With the TIME() function, convert the text to a desired TIME format.
      1. TIME(text, 0) for 5:10 PM format
      2. TIME(text, 1) for 17:10 format

    However, this may be useless if your system time column displays a different time zone.

    You can view the column formula, the fx, or the "column description" of the published sheet below.

    https://app.smartsheet.com/b/publish?EQBCT=1b2db087160b4a2e8f6a459e069667ef
    image.png


    · Share on FacebookShare on Twitter
  • PM_Reeves
    PM_Reeves ✭✭✭✭

    Thanks again @jmyzk_cloudsmart_jp ,

    I was asked to remove the time function so now have =DATEONLY(Modified13) + "" as the cell formula.

    This formula is consistent across all 80 Projects I have on the go. Yet some display the results in UK date format, some US and it appears to be triggered by updating the referenced cell. This cell only has text.

    The example you supplied is what I see in all sheets except the 'report' sheet, where 04/12/2023 reverts to 12/04/2023.

    Have a great day.

    Cheers

    · Share on FacebookShare on Twitter
  • PM_Reeves
    PM_Reeves ✭✭✭✭

    Hi @Genevieve P. ,

    Would you have any insight into this issue?

    The crux of the matter is that one sheet doesn't seem to be able to maintain a consistent format for dates.

    The column feeds in from individual sheets, which all have UK/AUS format, but when updating text in the cell which feeds this formula =DATEONLY(Modified13) + "" it causes the cell in the target sheet to display in US date format.

    Yet if I remove the DATEONLY from the formula, it reverts to UK/AUS but I have the time which I don't want.

    Thanks

    · Share on FacebookShare on Twitter
  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭

    Hi @PM_Reeves

    I realized I was writing a nonsensical answer when I looked into the American and British DATE formats.

    image.png

    The month and day order differs in American and British formats.


    In that case, for system values like Modified and Created, I will first convert them into strings for processing, as these are displayed in local time when shown on the sheet. However, they are converted to GMT for calculations when used in formulas.

    image.png

    In Japan, GMT+9, DATEONLY(Created) will return the previous day's date if it is before 9 am.


    To reliably convert to the desired British format, I will use the TEXT function to extract and use the date portion from the converted string date.

    =LEFT(Modified@row, 8)


    The regional preference of the sheet below is English (United States), but the formula should work in different settings.

    https://app.smartsheet.com/b/publish?EQBCT=1b2db087160b4a2e8f6a459e069667ef
    image.png


    · Share on FacebookShare on Twitter
  • PM_Reeves
    PM_Reeves ✭✭✭✭

    Hello again @jmyzk_cloudsmart_jp

    Thanks again for your input - '=LEFT(Modified@row, 8)' seems to work (so far), but then again so did '=Modified@row + ""' until strange things started happening. 😀

    It's not so much the formulas that are the issue, I just can't fathom how Smartsheet displays the same information inconsistently. Even more so given the fact it was all in sync until updates were made, but only to a cell that fed the formula, not the formula itself.

    If they could just add in right-clicking 'Copy & Paste' for everything rather than a select few functions when they fix this, that'd be great. 🙄


    Have a great day

    · Share on FacebookShare on Twitter
  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭

    Hi @PM_Reeves

    I came across this article.

    Pacific Time overrides

    When changes are made to a sheet via automation, cell links, or cross-sheet formulas, the whole sheet is converted to Pacific Time. Automation services are hosted by service users with a Pacific Time setting. This means that TODAY() formulas and display system column display values may get updated if you're in a different time zone.


    https://help.smartsheet.com/articles/2482412-how-smartsheet-handles-time-zones


    The above-quoted part may explain your issue below.

    This cell with the update time is linked into another sheet, yet this displays in US format, even though other rows display in UK format. It's the cell with content in the cell to the left. And it takes US Pacific Time.

    · Share on FacebookShare on Twitter
  • PM_Reeves
    PM_Reeves ✭✭✭✭

    Thanks @jmyzk_cloudsmart_jp

    I have noticed a few more people commenting about similar issues so I had linked your responses to my query. I've even tagged a couple of Smartsheet staff in my question, but no response from them.

    Hope you're well.

    · Share on FacebookShare on Twitter

Categories

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video