Saved Filters Issue Date Type Column

Ajamieson

I'm running into a very odd issue with consistent filtering on a date column thats being generated from a formula:

We have a column labeled "Filter date" that is set to Date (Restrict to dates only not checked)

which has the following formula to trim off the date from a 2ndary column that is a drop down:

=IF([Date Scheduled]1 = "Complete Parts To Inspection", "", IF([Date Scheduled]1 = "Complete Tool back to Tool Room", "", IF([Date Scheduled]1 = "Parking Lot", "", IF([Date Scheduled]1 = "TBD May", "", IF([Date Scheduled]1 = "TBD January", "", IF([Date Scheduled]1 = "TBD February", "", IF([Date Scheduled]1 = "TBD March", "", IF([Date Scheduled]1 = "TBD April", "", IF([Date Scheduled]1 = "TBD June", "", IF([Date Scheduled]1 = "TBD July", "", IF([Date Scheduled]1 = "TBD August", "", IF([Date Scheduled]1 = "TBD September", "", IF([Date Scheduled]1 = "TBD October", "", IF([Date Scheduled]1 = "TBD November", "", IF([Date Scheduled]1 = "TBD December", "", IF([Date Scheduled]1 = "", "", RIGHT([Date Scheduled]1 + "", LEN([Date Scheduled]1) - 3)))))))))))))))))

This formula looks at the date in date scheduled and does several If statements to yield the desired date only from that column.

The issue we are running into is that we cannot filter on the "filter date" column consistently with either manual filters or saved filters. Here you can see with the "2 Day View" filter selected it seems to give us no results. 

Now to make things even more interesting, if i select the "Filter date" column and change its properties from date to text/number, and then change them back to date. the filters suddenly work

Then once you save the sheet, reset the filter and go back to the 2 day view, you once again get zero results.

Any help on this would be greatly appreciated and if anyone has a similar issue please let me know what you have done to work around it.

 

 

J. Craig Williams

Andrew,

What you seeing is likely due to seeing something that looks like a date but is not a date, it is text.

Even in a Date column,

RIGHT([Date Scheduled]1 + "", LEN([Date Scheduled]1) - 3)

will return TEXT format.

And filters are easily fooled.

It would be better to have [Date Scheduled] be a true date and add a column to show Day of Week + Date if that is useful for you but to use the true date in your formula.

Also, I would change all of this:

IF([Date Scheduled]1 = "TBD May", "", IF([Date Scheduled]1 = "TBD January", "", IF([Date Scheduled]1 = "TBD February", "", IF([Date Scheduled]1 = "TBD March", "", IF([Date Scheduled]1 = "TBD April", "", IF([Date Scheduled]1 = "TBD June", "", IF([Date Scheduled]1 = "TBD July", "", IF([Date Scheduled]1 = "TBD August", "", IF([Date Scheduled]1 = "TBD September", "", IF([Date Scheduled]1 = "TBD October", "", IF([Date Scheduled]1 = "TBD November", "", IF([Date Scheduled]1 = "TBD December", ""

to

IF(LEFT([Date Scheduled]@row,3) = "TBD"),"", ...

 

Craig

 

Ajamieson

Thank you Craig, I will work this into the solution, your the best!

Ajamieson

Thinking outside of the box on this; we are using the Date Scheduled column as a queue management header for card view so it has non date text in there for queue management in our process, so starting with a true date is not an option.

But what i managed to do was on a new/blank sheet create a text column using the fri 5/18/18 format then the next column is a true date column showing the dates then i spanned out 1 years worth of dates. created a vlookup to inject into the filter date column on the original page and this solved the filtering issue! Thank you for the input it allowed me to see the limitations more clearly and work within them. 

-Andrew

J. Craig Williams

A year of date is overkill, I think. I am not sure it if saves on browser processing either.

But glad it works.

Craig

Howard Pyett

Ran in to the same issue ... again, now understanding that Smartsheet will only filter on TRUE dates, the challenge was to find the formula to extract the text from a US format (m/d/yyyy) input free text cell, convert to UK format d/m/yyyy and also turn it in to a TRUE Date. That was the clue to me, DATE:

The [Start Time] was in variable format m/dd/yy, mm/d/yy, m/d/yyyy plus a time - for example 6/17/2020 00:01, or it could be 06/17/20 00:01 etc.

I effectively had to break it down to provide a DATE(year, month, day) - and extraction VALUE formula for the 'year', 'month and 'day'.

DATE(VALUE(extracted year formula),VALUE(extracted month formula),VALUE(extracted day formula) - the below formula looks busy...cos it is, but it works

=IFERROR(IF(FIND("/", [Start Time]1) > 1, DATE(VALUE(RIGHT((MID([Start Time]1, FIND("/", [Start Time]1) + 1, FIND("/", [Start Time]1, FIND("/", [Start Time]1) + 1) - FIND("/", [Start Time]1)) + LEFT([Start Time]1, FIND("/", [Start Time]1)) + "20" + MID([Start Time]1, FIND(" ", [Start Time]1) - 2, 2)), 4)), VALUE(MID((MID([Start Time]1, FIND("/", [Start Time]1) + 1, FIND("/", [Start Time]1, FIND("/", [Start Time]1) + 1) - FIND("/", [Start Time]1)) + LEFT([Start Time]1, FIND("/", [Start Time]1)) + "20" + MID([Start Time]1, FIND(" ", [Start Time]1) - 2, 2)), 4, 2)), VALUE(LEFT((MID([Start Time]1, FIND("/", [Start Time]1) + 1, FIND("/", [Start Time]1, FIND("/", [Start Time]1) + 1) - FIND("/", [Start Time]1)) + LEFT([Start Time]1, FIND("/", [Start Time]1)) + "20" + MID([Start Time]1, FIND(" ", [Start Time]1) - 2, 2)), 2))), ""), "Invalid date")