Help with (date) helper column anomaly
I am using a helper column to provide a date from 'Date created' column that auto adds date and time. The helper column date can then be used to calculate days btw it and today.
I am using the formula =DATEONLY([Date created]@row) but the helper column shows the day before as in the screenshot below.
I have experienced the same issue using the same sheet set up previously.
Hope not but could it relate to me being in Australia and some time difference (with what) issue?
Any ideas anyone?
Best Answers
-
Hello @Phil Wightman
Yes, the issue you are observing is typically caused by timezone issues. The issue is caused by the fact that, although the Date Created appears to be in your timezone, the date is actually stored as UTC(0). The discrepancies occur when rows are being created when UTC has already crossed midnight and your local timezone has not- or vice versa. I run into this issue with my sheets that are being used by 24-7 manufacturing operations and data is being generated as the UTC crosses midnight.
My workaround is to use the Record Date automation to record the date in a Date Helper-type column, triggering off of a change in Created Date column. This will keep the date always in my local timezone.
Kelly
-
Hey Phil
I use the Record Date in place of the DATEONLY(Created@row). Then you can set up your No of Days formula to use that Recorded Date as your start date. For the automation, the trigger can be set for When A Row is Created. Using my approach, you would have the Record Date insert the date in your Date Helper column.
If I didn't explain something well, don't hesitate to ask again
Kelly
-
I'm not sure. I'll have to test tonight during my discrepancy window and report tomorrow. My gut/memory says it doesn't flip but I will verify. My gut/memory tend to frequently be wrong. 😉
-
Hey Phil
It looks like I remembered correctly. I confirmed, the dates didn't change once the time zone caught up. To make sure I had correctly calculated when my timezone discrepancy occurs, I added a column in my sheet called [IS TODAY]. (The discrepancy window shifts by 1hr during daylight savings time.)
=IF(Created@row=TODAY(), 1). The TODAY() function captures the date that is true to one's timezone.
I added new rows both last night and tonight during 'my timezone discrepancy window'. My Created dates appeared as the correct local date the entire time (the created date of rows I created last night inserted what appears as the local date/time and that date/time has not changed; however, my [IS TODAY] checkbox column is now checked for the rows I created last night (it was before my local midnight). To restate- the date and timestamp shows yesterday but [IS TODAY] says it is Today. Since, as I type this, I am inside my 'timezone discrepancy window', any new row I am creating now is not checking [IS TODAY] even though locally I haven't crossed midnight yet.
Kelly
Answers
-
Hello @Phil Wightman
Yes, the issue you are observing is typically caused by timezone issues. The issue is caused by the fact that, although the Date Created appears to be in your timezone, the date is actually stored as UTC(0). The discrepancies occur when rows are being created when UTC has already crossed midnight and your local timezone has not- or vice versa. I run into this issue with my sheets that are being used by 24-7 manufacturing operations and data is being generated as the UTC crosses midnight.
My workaround is to use the Record Date automation to record the date in a Date Helper-type column, triggering off of a change in Created Date column. This will keep the date always in my local timezone.
Kelly
-
Hi @Kelly Moore , thanks for the response and solution. Can you step out what I need to do as I dont follow. EG do I still have my help on the created on or is what you suggest in place?
The aim is to be able to capture the no of days btw date created and today. ATM the calculation used the helper and today's date columns. What does your solution use to make the calculation please.
-
Hey Phil
I use the Record Date in place of the DATEONLY(Created@row). Then you can set up your No of Days formula to use that Recorded Date as your start date. For the automation, the trigger can be set for When A Row is Created. Using my approach, you would have the Record Date insert the date in your Date Helper column.
If I didn't explain something well, don't hesitate to ask again
Kelly
-
Hi @Kelly Moore
Does the date anomaly correct itself as time zones catch up?
-
I'm not sure. I'll have to test tonight during my discrepancy window and report tomorrow. My gut/memory says it doesn't flip but I will verify. My gut/memory tend to frequently be wrong. 😉
-
Hey Phil
It looks like I remembered correctly. I confirmed, the dates didn't change once the time zone caught up. To make sure I had correctly calculated when my timezone discrepancy occurs, I added a column in my sheet called [IS TODAY]. (The discrepancy window shifts by 1hr during daylight savings time.)
=IF(Created@row=TODAY(), 1). The TODAY() function captures the date that is true to one's timezone.
I added new rows both last night and tonight during 'my timezone discrepancy window'. My Created dates appeared as the correct local date the entire time (the created date of rows I created last night inserted what appears as the local date/time and that date/time has not changed; however, my [IS TODAY] checkbox column is now checked for the rows I created last night (it was before my local midnight). To restate- the date and timestamp shows yesterday but [IS TODAY] says it is Today. Since, as I type this, I am inside my 'timezone discrepancy window', any new row I am creating now is not checking [IS TODAY] even though locally I haven't crossed midnight yet.
Kelly
-
Thanks for the detailed follow up @Kelly Moore
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!