Why is DATEONLY() returning the wrong date?
Hello All,
Does anyone understand the behavior of the highlighted cell below? I believe it should be displaying "08/18/20".
Best Answers
-
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.
-
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)))
Answers
-
Hi Brian,
This is an odd thing. Try
=DATEONLY(Date@row)
That may help for it to select the correct row...possibly.
-
Hi Michele,
Thanks for the suggestion, but that didn't work either.
-
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.
-
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.
-
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.
-
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?
-
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)))
-
@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.
-
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....
-
Yes, time zone handling in Smartsheet really needs a global solution!
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.
-
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.
-
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.
-
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)
-
-
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.
Help Article Resources
Categories
Check out the Formula Handbook template!
