Date Range Formula
Hello,
I am having an issue when trying to return values like "FY22" based on a date range. What I need is that is the date is between FY 06/01/2021 and 05/31/2022 for it to return FY22, if not then x. I get #Invalid Operation, and I've tried many different ways. This is what I have now:
=IF(AND([FY Eligible Date]@row >= DATE(2021, 6, 1), [FY Eligible Date]@row <= DATE(2022, 5, 31)), "FY22", "x")
Comments
-
And FY Eligible Date is a date type column?
-
It's hard to tell from the screenshot...but if you built the formula outside of smarthsheet, I'd retype all of your quote marks in smartsheet.
-
Mike,
That was my first guess. I zoomed WAY in though, and they look fine.
-
I zoomed way in without loading it in a new tab and I couldn't tell. I should have tried opening it in a new window. It must be the column type. I don't see any other reason for that error.
-
Yes it is.
-
Hey Mike,
I retyped everything IN smartsheet and still nothing. FY Eligible Date column type is Date and the FY column type is text/Number.
I'm not sure what else it can be?!
-
I used your original formula and tried it out in my own test sheet. It worked perfectly with a single date column and a number/text column. See the screenshot below.
-
Double-check both column types to make sure they are accurate and try creating new columns and setting up the data again. This is weird.
-
Hmm... Shots in the dark here...
Try changing the dates.
Try reversing the order of the dates so that <= is first and >= is last within the AND statement.
Try changing your outputs.
Try replacing the cell reference with a DATE function.
.
I am not sure if any of these will work, but if we start changing things up one at a time, we may be able to pinpoint exactly where the error is coming from.
Just be sure to only change one thing at a time. If it doesn't fix it, change it back. Also be sure to keep proper syntax and whatnot within the changes to avoid accidentally creating a different error.
-
@Paul, I created both of those columns in a test sheet and used the original formula that she posted. It worked on the first paste. it must be something with the columns. I don't know what else it would be.
-
This almost reminds me of some issues I have had in the past using dates in COUNTIFS functions. If I relocated the range/criteria containing the dates to a different placement within the COUNTIFS, it would work just fine.
I can't remember who else had the issue, but we ended up coming to the conclusion that if our range/criteria containing dates was first, we would get an #INVALID OPERATION error.
If we moved it to the second, third, etc set of range/criteria, it would suddenly work just fine.
Support couldn't even figure out why it was working that way.
.
Which reminds me...
Double click into the date cell as if you are going to edit it, then click out of it. This will essentially "refresh" the cell.
I had an issue a while back where a date in a date type column was (for whatever reason) being treated as text instead. The solution was to enter the cell as if I were going to edit it then exit the cell to refresh the data within, and it worked for that particular issue.
-
Hey Mike and Paul,
So I think what the issue I am having is that the FY Date Calculation column itself has a formula in it because when I put in the FY Eligible Date manually my FY formula works. But that doesn't help me.
-
Yeah, I think it's because I have a date calculation in my FY Eligible Date... How strange and I wonder how I'm going to work around it. Even if I copy over the date into a new cell using = and cell reference it gives me the error. I only by-pass the error when the date is manually entered without any formulas.
-
-
Is the original column where the date is coming from a date column? I did a manual test creating the date by a formula... It worked for me.
Help Article Resources
Categories
Check out the Formula Handbook template!
