Help with (date) helper column anomaly

Phil Wightman

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?

Kelly Moore

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

Kelly Moore

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

Kelly Moore

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. 😉

Kelly Moore

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

Kelly Moore

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

Phil Wightman

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.

Kelly Moore

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

Phil Wightman

Hi @Kelly Moore

Does the date anomaly correct itself as time zones catch up?

Kelly Moore

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. 😉

Kelly Moore

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

Phil Wightman

Thanks for the detailed follow up @Kelly Moore