Date Range Formula

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!

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