Created Date Formula Providing Next Day

Options
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 ✓
    Options

    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 ✓
    Options

    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)

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

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

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

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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.

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

    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 ✓
    Options

    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
    Options

    @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 ✓
    Options

    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)

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

    Thank you Paul!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!