Query on Created Date Returns incorrect results

Karen Hansard

Hello and Happy New Year! I have a sheet with a Created date column defined as below. I've created a filter to return all rows "greater than" a specific date in this column however the filter returns rows greater than or equal to the date in the filter. Is this because there is a time stamp and anything greater than 00:00 is returned? If that is the case, how can I work around this?

Thank you in advance for you help!

Mark Cronk

Hi @Karen Hansard ,

Try inserting a date helper column [date only] and using that in your filter. In the [date only] column enter the formula =DATEONLY(Created@row)

This formula removed the time stamp.

Work?

Mark

Karen Hansard

Hi Mark,

Thanks for the suggestion. I thought for sure that would work but the resulting date in my Filter Date column doesn't equal the date in the Created column :( It did remove the timestamp though! I'm wondering if this is a timezone issue. All the dates translate properly if the timestamp is less than 9pm. I'm on the East coast. Is there a timezone setting in Smartsheet?

Paul Newcome

It very well could be a timezone issue. There are a few solutions to this problem floating around here in the community. I'll see if I can find one and post a link for you...

Paul Newcome

I can't seem to find the particular link I am looking for, so here is a formula that should work to populate the correct date...

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

You are going to want to change the bold 7 to whatever hour seems to be the switchover for your particular timezone.

Karen Hansard

Hi Paul

Thanks! I'll give your formula a try. It does seem like a Smartsheet bug though.

Paul Newcome

It is a bit of a known bug unfortunately.