Help with (date) helper column anomaly

Options
Phil Wightman
Phil Wightman ✭✭✭✭✭✭
edited 09/30/22 in Formulas and Functions

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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer βœ“
    Options

    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
    Kelly Moore ✭✭✭✭✭✭
    Answer βœ“
    Options

    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
    Kelly Moore ✭✭✭✭✭✭
    Answer βœ“
    Options

    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
    Kelly Moore ✭✭✭✭✭✭
    Answer βœ“
    Options

    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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer βœ“
    Options

    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
    Phil Wightman ✭✭✭✭✭✭
    Options

    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
    Kelly Moore ✭✭✭✭✭✭
    Answer βœ“
    Options

    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
    Phil Wightman ✭✭✭✭✭✭
    Options

    Hi @Kelly Moore

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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer βœ“
    Options

    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
    Kelly Moore ✭✭✭✭✭✭
    Answer βœ“
    Options

    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
    Phil Wightman ✭✭✭✭✭✭
    Options

    Thanks for the detailed follow up @Kelly Moore

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!