filter excel column based on multiple criteria - with mixed value type

Question

I need to filter an Excel column that contains dates and text.

For example if an item was on a shelf for over 30 days, I want to know about it.

I have a column for date put on shelf and another that contains date taken off shelf or text 'on shelf'

I can filter this fine if there is no text in the column but once an item stays on the shelf, the formula breaks and a value error is shown.

please help

=FILTER(Item_name , Date_off - Date_on >= 30) gives an array of all items that were on shelf for 30+days as long as no items are still 'on shelf'

I've thought about IF(ISNUMBER(Date_off), FILTER...) but not had much luck

Answer 1

You are on the right path:

=FILTER(tblData[item_name],IF(ISNUMBER(tblData[date_off]),tblData[date_off],0)-tblData[date_on]>=30)

checks for a numeric value - if it is text 0 is used to run the calculation.