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

Find more posts tagged with

Formulas

IT & Operations

Comments

Paul Newcome

What is the formula that produces your date?

Paul Newcome

Haha. Yeah. I have run into some pretty "interesting" glitches dealing with dates.

One of them was to use the DATEONLY function wrapped around my @cell (which was already in an IFERROR statement).

Me: But I'm not using a date/time column. Just a plain old manually entered date in a plain old date type column.

Support: Even though the DATEONLY function was designed (and all documentation supports this) to be used for a date/time type of column, using it in this case forces the formula to ignore those cells within the range that are blank or text.

Me: But isn't that what my IFERROR statement that's already built into the formula is supposed to do?

Support: Well yeah, but........ Using the DATEONLY is the only way we could get it to consistently work.

Me: Ok. I guess DATEONLY it is then.

And this was over the course of multiple emails per day for several days. Never did figure out WHY it was happening. Just that it does happen and I needed to use a workaround.

So to get consistent results with no random errors, put your date range/criteria set(s) anywhere within your COUNTIFS except for first, and even if you are referencing a regular date type column, double up on the DATEONLY and IFERROR just to be sure.

=COUNTIFS({Some other range}, @cell = "Some other criteria", {Date Range}, IFERROR(MONTH(DATEONLY(@cell)), 0) = 12)

It was a mess...

Andrée Starå

Hahahaha!

Mike Wilday

@Andree, did you watch that first gif long enough?

Andrée Starå

I did and that's why!

lol

Hammermill21

Hey Paul,

This thread has turned out to be pretty funny

I tried the date columns two different ways, one I used this formula to get the date:

=[Refresh Eligible Month]@row + "/" + [Refresh Eligible Day]@row + "/" + [Refresh Eligible Year]@row

This always returns the "#Invalid Operation error.

Then I tried by just creating another column to equal the date without the formula above:

=[FY Date Calculation]@row

Again I get the same error in trying to get the FY. I don't want to have to manually enter the date, to get the FY. If I have any type of formula in the date column the FY column gives me an error.

Make sense? This shouldn't be so complicated

Paul Newcome

Ah ha!!! That's it!

You formula is not actually producing a date. Your formula is producing a text string that just looks like a date when it is all put together.

Replace it with a DATE formula, and you should have smooth sailing...

=DATE([Refresh Eligible Year]@row, [Refresh Eligible Month]@row, [Refresh Eligible Day]@row)

Hammermill21

UGH! How foolish of me!!!!!

THANK YOU SO MUCH

Sign in to join the conversation!

Quick Links

Trending discussions

Compare Previous Week
Hello, I am trying to be able to say whether hours went up or down as compared to the previous week but am having trouble referencing the previous week. I have tried to add a helper column but am still getting an error. Any help would be greatly appreciated!
SUMIFS Formula with Date Column
Hello All, I am using this formula " =SUMIFS({RCX- Kit Forecast - 2025 Range 1}, {RCX- Kit Forecast - 2025 Range 2}, ">=" + TODAY(), {RCX- Kit Forecast - 2025 Range 2}, "<=" + (TODAY() + 5)) " Range 1 is the number column Range 2 is a date column But the value is coming as zero
Workflow to trigger comms when number exceeded but not in between.
Hi, trying to work out how I can get a workflow to trigger only when certain milestone numbers are met. For example, I'd like an email comm to be sent once the sum of a column reaches 50, 100, 150, 200 etc. The number will be entered via a form, very basic, so for example 4 entries could be sent via 4 form inputs (15, 11,…