Why is DATEONLY() returning the wrong date?

Brian Cianciolo

Hello All,

Does anyone understand the behavior of the highlighted cell below? I believe it should be displaying "08/18/20".

Paul Newcome

Basically the logic behind the solution is pulling the hour then saying that if it is greater than or equal to the hour that starts causing issues (but does not equal twelve) and "pm", then subtract 1 from the date.

I'll take a stab at it even though I haven't gone through my notes yet to at least get you started on the right track.

Pull the Hour:

=VALUE(MID(Date@row, 10, FIND(":", Date@row) - 10))

If the hour is greater than or equal to 7 and does not equal 12 and the time is PM, then subtract 1 from the original date.

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

For the above you will need to figure out which hour of the day is where the change needs to start occurring and then change the 7. I just used that as an example. Based on your screenshot it looks like 4pm is good but 9pm is not. You will want to test each of the hours in between to figure out what that number should be.

L_123

My understanding of this is they are on pacific time as smartsheet is based out of seattle. So the datetime in the background is always going to be pacific time, and is converted to local time for the frontend.

Left should work, if it didn't then Paul's solution wouldn't work as it isn't any different than pulling the mid

If you need it in date format you can use something like this

=DATE(VALUE(20 + MID(Date@row, 7, 2)), VALUE(LEFT(Date@row, 2)), VALUE(MID(Date@row, 4, 2)))

Michele Thomas

Hi Brian,

This is an odd thing. Try

=DATEONLY(Date@row)

That may help for it to select the correct row...possibly.

Brian Cianciolo

Hi Michele,

Thanks for the suggestion, but that didn't work either.

Paul Newcome

It very possibly has to do with Time Zones. I have seen this a handful of times elsewhere throughout this Community. If you do a search for posts related to time zones you should be able to find a few different options for solutions.

Brian Cianciolo

Thanks Paul, I can definitely see this as being the issue. I'll try to do some more testing and let you know what I find.

Paul Newcome

Basically the logic behind the solution is pulling the hour then saying that if it is greater than or equal to the hour that starts causing issues (but does not equal twelve) and "pm", then subtract 1 from the date.

I'll take a stab at it even though I haven't gone through my notes yet to at least get you started on the right track.

Pull the Hour:

=VALUE(MID(Date@row, 10, FIND(":", Date@row) - 10))

If the hour is greater than or equal to 7 and does not equal 12 and the time is PM, then subtract 1 from the original date.

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

For the above you will need to figure out which hour of the day is where the change needs to start occurring and then change the 7. I just used that as an example. Based on your screenshot it looks like 4pm is good but 9pm is not. You will want to test each of the hours in between to figure out what that number should be.

Brian Cianciolo

Wow thanks Paul! This solution seems to do the trick (after I tune in the time values, of course). I do have a question about it: does "=LEFT(Date@row,8)" do the trick or is there an instance where that wouldn't work?

L_123

My understanding of this is they are on pacific time as smartsheet is based out of seattle. So the datetime in the background is always going to be pacific time, and is converted to local time for the frontend.

Left should work, if it didn't then Paul's solution wouldn't work as it isn't any different than pulling the mid

If you need it in date format you can use something like this

=DATE(VALUE(20 + MID(Date@row, 7, 2)), VALUE(LEFT(Date@row, 2)), VALUE(MID(Date@row, 4, 2)))

Paul Newcome

@L@123 I feel like this had been tried in a previous post and it was still pulling the wrong date because of how it was stored on the back-end. That's why I went with the solution I used. My solution mostly ignores the date by looking at the time instead.

L_123

I see what you are saying, I didn't look closely enough at your formula. Yeah I would double check to make sure that the left actually does correctly populate.

We really need some major enhancements to time functionality in smartsheet....

Stefan

https://community.smartsheet.com/discussion/comment/260373#Comment_260373

Yes, time zone handling in Smartsheet really needs a global solution!

BD

https://community.smartsheet.com/discussion/comment/260346#Comment_260346

Hello @Brian Cianciolo . Did your solution of using the Left Function work? Currently this is my workaround, but, I was curious to see if you had any issues using the Left Function. Thank you.

Brian Cianciolo

Hi @BD . The left solution is working fine for me. If there is an instance where it hasn't worked; I have yet to notice it.

BD

https://community.smartsheet.com/discussion/comment/274424#Comment_274424

Thanks Brian.

In the end, I had to use the below formula from Paul above in order to get =yearday function to work. . Thanks Paul.

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

Paul Newcome

Happy to help. 👍️

James Pedersen

I am in Pacific Time Zone and am running into the same issue with DATEONLY().

After some trial and error I found the date to be off by 7 hours, so the DATEONLY() will give the date at 0:00 GMT.

I added another AND() expression to @Paul Newcome's formula to account for this but also to account for the 12pm hour.

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

If it doesn't work for you, just try adjusting the number in the first expression (5 in my example)

Hope that helps.