Created Date Formula Providing Next Day

Michelle Choate 2

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?

Michelle Choate 2

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

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)

Paul Newcome

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

Michelle Choate 2

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

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.

Michelle Choate 2

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

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

@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

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

Thank you Paul!

Paul Newcome

Happy to help. 👍️