Created Date Formula Providing Next Day

Michelle Choate 2
Michelle Choate 2 ✭✭✭✭✭
edited 07/06/23 in Formulas and Functions

So I have the created date automatically filling itself in the column "Created" and then a formula DATEONLY(Created@row) to give me the date only to use in formulas on other sheets. My issue is that the DATEONLY formula is sometimes giving me the next day! And only if it is submitted in the afternoon or evening. I figured after 9pm PST it is next day in EST so that is fine. But why is this at 6pm PST?


Best Answers

  • Michelle Choate 2
    Michelle Choate 2 ✭✭✭✭✭
    edited 07/06/23 Answer ✓

    So looking at other comments, I found the following formula that will work for my Pacific (GMT +7) Timezone:

    =DATEONLY(Created@row) - IF(AND(FIND("P", Created@row) > 0, VALUE(MID(Created@row, FIND(" ", Created@row) + 1, 1)) >= 5, VALUE(MID(Created@row, FIND(" ", Created@row) + 1, 1)) < 12), 1)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    The DATEONLY used to be PST based if I remember correctly. I'm not sure when it changed to GMT.


    Having said that... The only adjustment I would make to your formula is accounting for a two digit hour like so:

    =DATEONLY(Created@row) - IF(AND(FIND("P", Created@row) > 0, VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1))) >= 5, VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1))) < 12), 1)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    What time zone are you in, and what time zone is your account set for?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Michelle Choate 2
    Michelle Choate 2 ✭✭✭✭✭
    edited 07/06/23

    I am in PST and my account is set for PST.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    That's odd. Usually the switch is based on PST. Is it consistently any time after 6PM? If so, we can write a formula to accommodate and output the correct date.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Michelle Choate 2
    Michelle Choate 2 ✭✭✭✭✭
    edited 07/06/23

    Yes it is. Actually it is any time after 5pm PST. I would love help with a formula! It looks like it clicks over to the next day at 0 GST. Could it be that my account may be set in PST but the formulas look at GST?

  • Michelle Choate 2
    Michelle Choate 2 ✭✭✭✭✭
    edited 07/06/23 Answer ✓

    So looking at other comments, I found the following formula that will work for my Pacific (GMT +7) Timezone:

    =DATEONLY(Created@row) - IF(AND(FIND("P", Created@row) > 0, VALUE(MID(Created@row, FIND(" ", Created@row) + 1, 1)) >= 5, VALUE(MID(Created@row, FIND(" ", Created@row) + 1, 1)) < 12), 1)

  • jessica.smith
    jessica.smith ✭✭✭✭✭
    edited 07/06/23

    @Michelle Choate 2 The Created timestamp will be based in your timezone, but the DATEONLY formula wont. I believe the DATEONLY formula returns GMT time, which is 7 hrs ahead of PST.

    Some other options:

    -use a Smartsheet workflow to record a date when a new row is added

    -add a helper column that extracts the hour and then use a modified formula in your Created Date column to subtract 1 day from the date to account for the timezone difference if the timestamp is after a certain time in the afternoon.

    Somthing like:

    To extract the hour from the Created column, create an "Hour" helper column with the following column formula

    =MID(Created@row, 9, FIND(":", Created@row) - 9)

    Reads as, starting at position 9, return the number of characters between position 9 and the colon

    This will return the one or two characters that correspond to the hour

    MM/dd/yyyy hh:mm PM vs MM/dd/yyyy h:mm PM


    To return the created date in your timezone

    =IF(AND(RIGHT(Created@row, 2) = "PM", Hour@row > 4), Created@row - 1, Created@row)

    Reads as, if the created timestamp includes PM and the hour is greater than 4 (i.e., 4 PM GMT), return the date in the created column minus 1 to adjust for the timezone, otherwise just return the date in the created column

    Notes:

    You might be able to include the hour extraction formula in your Created Date column formula, but I was having a difficult time getting Smartsheet to play nice with the combined formulas.

    Give some thought to whether 4 PM GMT Is the right cut off time...I've never been great with timezones 😂

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    The DATEONLY used to be PST based if I remember correctly. I'm not sure when it changed to GMT.


    Having said that... The only adjustment I would make to your formula is accounting for a two digit hour like so:

    =DATEONLY(Created@row) - IF(AND(FIND("P", Created@row) > 0, VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1))) >= 5, VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1))) < 12), 1)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Michelle Choate 2
    Michelle Choate 2 ✭✭✭✭✭

    Thank you Paul!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!