DateOnly YearDay off a day Time Zone Question

BD

Hello. I have a question about the DateOnly and YearDay functions. Please see below (Pic 1). All dates after 7pm gets moved to the next day for the DateOnly and YearDay functions. I am assuming, not 100%, this is a Time Zone issue as I live in UTC -5 hours.(Pic 2). I saw a post (Pic3) that said that Smartsheet captures dates and times in UTC but displays the value accordingly to your personal setting. Since I live in UTC time zone and my personal settings are set to UTC, What else could cause this? Thank you.

Computer Settings

Previous post in 2018

Thank you.

Mark Cronk

Hi @BD ,

Looks to me like the timezone issue that many people have posted about. @Paul Newcome is the time expert. His response to the post below may lead you to a solution.

https://community.smartsheet.com/discussion/70719/why-is-dateonly-returning-the-wrong-date

I'm curious to see what others think.

Mark

BHarvey

Concatenating or simulating the date does not work when you have other columns that depend on a date, such as calculating hours and minutes etc.

I solved this by breaking down the date into it's component parts (Year, Month, Day, Hour, Minutes, AM/PM) and then used the following formula to ensure the correct date was being displayed in my Date Column:

=IF([AM PM]@row = "PM", IF(Hour@row >= 8, DATEONLY([Created Date]@row) - 1, DATEONLY([Created Date]@row)), DATEONLY([Created Date]@row))

BD

I also would like to add my settings for SmartSheet:

Thank you.

Mark Cronk

Hi @BD ,

Looks to me like the timezone issue that many people have posted about. @Paul Newcome is the time expert. His response to the post below may lead you to a solution.

https://community.smartsheet.com/discussion/70719/why-is-dateonly-returning-the-wrong-date

I'm curious to see what others think.

Mark

Kelly Moore

Hi BD

Yes, it's a timezone problem - I'm also UTC-5 and I first discovered it, just like you, when I noticed working formulas would return wrong value exactly at 7pm. Usually the timezone doesn't affect formulas except, for example, comparing the timezone date to Today() during the time period of UTC-0 until your timezone's midnight. So for the central timezone that's our 7pm-midnight.

I have verified that the new Record a Date automation is based on actual time zone. This might be a workaround for you if you have a trigger that you can capture. The output of this automation is a date, not a date stamp (with time). Interestingly if one extracts the time from the datestamps it remains in the correct timezone - it's just the date portion that has affected my formulas.

cheers,

Kelly

BD

Thank you @Mark Cronk and @KDM for your replies.

Thanks Mark, I just replied to the other thread as I am using the same Left function workaround as the OP in that thread.

Has anyone had any issues using =LEFT(Date@row, 8)? So far so good but I do not have enough data to verify.

BD

The above Left function did not work for when I tried to using the YearDay function. However, thanks to Paul's answer in another thread from link above, I ended up using the below formula

=YEARDAY(DATEONLY(Date@row) - IF(AND(VALUE(MID(Date@row, 10, FIND(":", Date@row) - 10)) >= 7, FIND("P", Date@row) > 0), 1))

Thanks Everyone.

Mark Cronk

Excellent. Glad you found a solution. Thank you for adding to the Community.

Mark

Paul Newcome

Glad you were able to find a working solution. 👍️

BD

Hello @Paul Newcome . I am realizing that this option does not always work for entries around 2 pm - 7pm. Sometimes, but not always, I can get it to work by:

• Cut and past the same formula in the cell.
• Sometimes reopening the sheet. This can work 1-2 hrs after entry.
• wait till 7 pm and it would be working correctly. It may not work the next day till 7pm or another option above.

I am using the Yearday function as criteria for a Sumifs formula.

I also noticed that:

• multiple days are affected. However, it would work one day and time and not another day and time.
• The YearDay would show the correct day in the cell but not always work in the sumifs formula.
• Entries before 12pm will always work.

Any help is really appreciated. Thank you

BD

Hello @Paul Newcome . Does this only occur for a cell / column that used the auto number created (Date) option? Is time associated with a basic column date property? Thank you.

BD

So far the formula below that was originally posted by Kennedy has been working for me for the past 10 days with no issues. Thank you. @Kennedy Stomps

=DATE(YEAR(Date@row), MONTH(Date@row), VALUE(VALUE(MID(Date@row, 4, 2))))

BHarvey

Concatenating or simulating the date does not work when you have other columns that depend on a date, such as calculating hours and minutes etc.

I solved this by breaking down the date into it's component parts (Year, Month, Day, Hour, Minutes, AM/PM) and then used the following formula to ensure the correct date was being displayed in my Date Column:

=IF([AM PM]@row = "PM", IF(Hour@row >= 8, DATEONLY([Created Date]@row) - 1, DATEONLY([Created Date]@row)), DATEONLY([Created Date]@row))