Created Date Formula Providing Next Day
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
michelle.choate@outlook.com
Always happy to walk through any project you need help with! Book time with me here: https://calendly.com/michelle-choate
Best Answers
-
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)
Michelle Choate
michelle.choate@outlook.com
Always happy to walk through any project you need help with! Book time with me here: https://calendly.com/michelle-choate
-
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
-
What time zone are you in, and what time zone is your account set for?
-
I am in PST and my account is set for PST.
Michelle Choate
michelle.choate@outlook.com
Always happy to walk through any project you need help with! Book time with me here: https://calendly.com/michelle-choate
-
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.
-
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
michelle.choate@outlook.com
Always happy to walk through any project you need help with! Book time with me here: https://calendly.com/michelle-choate
-
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)
Michelle Choate
michelle.choate@outlook.com
Always happy to walk through any project you need help with! Book time with me here: https://calendly.com/michelle-choate
-
@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 😂
-
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)
-
Thank you Paul!
Michelle Choate
michelle.choate@outlook.com
Always happy to walk through any project you need help with! Book time with me here: https://calendly.com/michelle-choate
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!