Differing time zones showing on linked cells
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.
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.
My overriding Timezone is set to Adelaide, with Regional Preferences set to English (Australia)
Can anyone explain what's going on, and any possible method of getting consistent date formats?
Thanks
Answers
-
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.
This is the source sheet with the same formula.
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.
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.
Then, the linked-in cell had a different format (US format), but the other cell became a 24:00 format.
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.)
This is the cell history on the target sheet.
This is the cell history on the source sheet. (link out sheet)
Then, this is the cell history after I set the language back to US English.
-
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.
-
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.
-
Hi @PM_Reeves
A new function, TIME(), may help you.
- Convert the System Time column to Text/Number by adding "".
- With the text function like the RIGHT() function, get the Time part text, "5:10 PM", for example.
- With the TIME() function, convert the text to a desired TIME format.
- TIME(text, 0) for 5:10 PM format
- 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.
-
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
-
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
-
Hi @PM_Reeves
I realized I was writing a nonsensical answer when I looked into the American and British DATE formats.
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.
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.
-
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
-
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.
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.
-
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.
