Returning Yes/No Value based on date columns
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!
Answers
-
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")
-
@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!
-
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?
-
@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?
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Try this:
=IF([Column 2]@row = "", "Yes", IF(AND([Column 1]@row < TODAY(-30), [Column 2]@row > TODAY(-30)), "Yes", "No"))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!