I have two columns that I have users enter data, formula combines it with the current modified date and time, then automations copy those rows to another sheet, so the history is preserved with the time. I then use the JOIN/COLLECT trick in new columns on the original sheet to show a log of that data change over time.
One of these columns is just regular text and copies over with the correct time stamp onto the second sheet. However, the second column is Latest Comment and this copies over 3 hours behind our real time zone on the second sheet. Back on the original sheet, the JOIN/COLLECT of course just pulls in the text so the history does not make sense.
I've confirmed my time zone in personal settings is correct (and regardless, the other cell+automation on the same sheet at the same time is referencing it without an issue). I also tried manually copying the row and did not select comments (which you can do on manual copying, but not automated copying it seems) and lo and behold, the time is totally correct.
This definitely feels like an unintended function or a bug for when the Latest Comment column is getting copied over. Has anyone else encountered this? Any workaround ideas? Thanks in advance!
Here is the original sheet showing the issue: