Date Range Formula

Hammermill21

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")

 

Paul Newcome

And FY Eligible Date is a date type column?

Mike Wilday

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. 

Paul Newcome

Mike,

 

That was my first guess. I zoomed WAY in though, and they look fine.

Mike Wilday

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. 

Hammermill21

Yes it is.

Hammermill21

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?!

Mike Wilday

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. 

 

Mike Wilday

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. 

Paul Newcome

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.

Mike Wilday

@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. 

Paul Newcome

@Mike,

 

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...

 

@Hammermill21,

 

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.

Hammermill21

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.

Hammermill21

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. 

Mike Wilday

@Paul... WHAATTTTT???????

Mike Wilday

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. 

 

• https://drive.google.com/a/lapu.edu/file/d/1cEldxt-ym8MHIfQgn5v5BuAYSZ9RdHOq/view?usp=drivesdk