Returning Yes/No Value based on date columns

C Rasp

I've tried multiple ways of putting together a formula, but I am stumped! The Excel version of the formula is not translating, so I'm not sure where to go from here. Basically I'm trying to use IF/AND to see if Column 1 is < 09/30/2022 (or whatever the last date of a month is as I update it), and Column 2 is >09/30/2022, and if so, it returns Yes if true, and No if false. The issues I'm running into is that Column 2 has a lot of blank cells because the end date is not yet determined. If it's blank, I'm wanting it to also return a Yes value.

The excel formula that is used that I'm trying to translate over is "=IF(AND([Column 1]@row<DATEVALUE("09/30/2022"),[Column 2]@row>DATEVALUE("09/30/2022"), "Yes", "no").

I know Date Value isn't an option in Smartsheet, and even when I try ISDATE, it still doesn't translate. All the relevant columns are in date format. Not sure what I'm missing here so any help would be great!

Bennett Breuklander

Not sure if this is exactly what you are looking for but I created a new column to act as the date value and it worked.

=IF(AND([Column 1]@row < [Date Search Column]@row, [Column 2]@row > [Date Search Column]@row), "Yes", "No")

There are a lot of ways to return the result you are looking for. If you don't want to create a new column for doing the "search", you can try using the TODAY() function. It just comes down to what exactly you are trying to accomplish.

=IF(AND([Column 1]@row < TODAY(-30), [Column 2]@row > TODAY(-30)), "Yes", "No")

C Rasp

@Bennett Breuklander I tried this, but it didn't work. Column 2 is a date generated from a formula. Is it possible that's what's giving me an invalid operation error? It also doesn't seem to want to close the AND function, but I can't figure out where I'm going wrong!

Bennett Breuklander

https://community.smartsheet.com/discussion/comment/347251#Comment_347251

Having any of the cells as formulas should not matter.

Do you have all columns that contain dates as the "Date" column type, including your search column?

C Rasp

@Bennett Breuklander I double checked and all my columns are date columns, except the final column I'm trying to have be Yes/No. If there is a value in the Filled/Closed row (Column 2), it runs correctly, but if it's blank, that seems to be what's causing the error. If it's blank, that's supposed to mean it's still open and should be active in the month, returning a Yes. Do I need to add a ISBLANK somewhere in the formula?

Genevieve P.

Hi @C Rasp

Blank date cells are seen as "in the past" which is likely why you're seeing different results than expected.

Yes, you can search if the cell is blank in your formula! I personally prefer looking for "", but you could use ISBLANK.

IF([Filled/Closed]@row = "", "Yes",

You can then add the statement to the front of your current formula, like this:

=IF([Filled/Closed]@row = "", "Yes", IF(AND([Recruiting Start Date]@row < [Start Date]@row, [Filled/Closed]@row > [Date Search]@row), "Yes", "No"))

Cheers,

Genevieve

Paul Newcome

Try this:

=IF([Column 2]@row = "", "Yes", IF(AND([Column 1]@row < TODAY(-30), [Column 2]@row > TODAY(-30)), "Yes", "No"))