IF AND Function Date Range Issues

[JoyeNeedsHelp]

I have too many parameters to use >=,<= to return values within dat
ranges entered in two cells; so, I created a "date range" output usin
the =TEXT(DATE(2013...) formula that looks like"06/01/14-06/30/13"
now...

What formula do I use to determine if the date in Cell A1 falls withi
the output date range located in C1

 

[Claus Busch]

Hi,

Am Thu, 22 Aug 2013 18:54:44 +0100 schrieb JoyeNeedsHelp:

I have too many parameters to use >=,<= to return values within date
ranges entered in two cells; so, I created a "date range" output using
the =TEXT(DATE(2013...) formula that looks like"06/01/14-06/30/13";
now...

try:
=(A1>=DATEVALUE(RIGHT(C1,8)))*(A1<=DATEVALUE(LEFT(C1,8)))
you get 1 if the date in A1 is wtihin the date range, else you get 0


Regards
Claus B.

 

[joeu2004]

I have too many parameters to use >=,<= to return values within date
ranges entered in two cells; so, I created a "date range" output using
the =TEXT(DATE(2013...) formula that looks like"06/01/14-06/30/13";
now...
What formula do I use to determine if the date in Cell A1 falls within
the output date range located in C1?

Not a good idea. I suggest that you go back to the original design and ask
for help with that.

I don't know what you mean by "too many parameters to use >=". More to the
point, I don't understand why expressing a date range as a string works, but
the following paradigm would not:

AND(DATE(2013,6,30)<=C1,C1<=DATE(2014,6,1))

Do you mean that you have too many nested functions in Excel 2003 or
earlier?

If that is the case, I doubt that any formula that parses the date string
and compares the resulting range would have fewer nested functions.

You can eliminate some levels of nested functions with any of the following
(not all recommended):

AND(--"6/30/13"<=C1,C1<=--"6/1/2014")

(--"6/30/13"<=C1)*(C1<=--"6/1/2014")=1

AND(A1<=C1,C1<=B1)

(A1<=C1)*(C1<=B1)=1

The latter two are preferred, where A1 is 6/30/2013 and B1 is 6/1/2014.

(The "=1" part might not be needed in some contexts.)

 

[JoyeNeedsHelp]

I was trying to reduce the complexity of the formula for ease o
translation, but I guess you need more input: The goal of the formul
is IF the harvest date as $E$4 is within the "month" (start 5/1/13 a
$G$82) (end 5/31/13 as $G$83), then return a revenue total, where $B$4
is the revenue per harvest.

=IF('SH Harv Table'!$E$4>=$G$82,'SH Harv Table'!$E$4<=$G$83,'SH Har
Table'!$F$4>=$G$82,'SH Harv Table'!$F$4<=$G$83,'SH Har
Table'!$G$4>=$G$82,'SH Harv Table'!$G$4<=$G$83,'SH Har
Table'!$H$4>=$G$82,'SH Harv Table'!$H$4<=$G$83,'SH Har
Table'!$I$4>=$G$82,'SH Harv Table'!$I$4<=$G$83,'SH Har
Table'!$J$4>=$G$82,'SH Harv Table'!$J$4<=$G$83,'SH Har
Table'!$K$4>=$G$82,'SH Harv Table'!$K$4<=$G$83,'SH Har
Table'!$L$4>=$G$82,'SH Harv Table'!$L$4<=$G$83,'SH Har
Table'!$M$4>=$G$82,'SH Harv Table'!$M$4<=$G$83))))))))),Shrimp!$B$49,0)

wherein I get a message "too many arguments for this function."
soooo, I tried adding a row that created the DATE RANGE in cell $G$8
resulting 5/1/13-5/31/13. ---WHAT FORMULA do I use????

.....Or I added a row that created the =MONTH for the month/date range
Then entered the following formula:

=IF((P91=MONTH(P83)),555,0)
where 555 represents harvest revenue -- but I am getting "false" fals
response

 

[joeu2004]

I was trying to reduce the complexity of the formula for ease
of translation, but I guess you need more input: The goal of
the formula is IF the harvest date as $E$4 is within the "month"
(start 5/1/13 as $G$82) (end 5/31/13 as $G$83), then return a
revenue total, where $B$49 is the revenue per harvest.

There must be something more to it that you are not saying, because your
requirements above are easily met with the following formula:

=IF(AND('SH Harv Table'!E$4>=$G$82,'SH Harv Table'!E$4<=$G$83),
Shrimp!$B$49,0)

Note that I wrote E$4, not $E$4. If you intend to copy the formula across
columns F through M, the formula will adjust accordingly. In fact, you
might even write E4 instead of E$4, if you also intend to copy the formula
down rows 5, 6, etc.

As written, the comparison is always with $G$82 and $G$83, and the non-zero
result is always Shrimp!$B$49. If you need those to change as you copy the
formula across columns or down rows, you need to give us more information
about how those references should change.

....Or I added a row that created the =MONTH for the month/date
range Then entered the following formula:
=IF((P91=MONTH(P83)),555,0)
where 555 represents harvest revenue -- but I am getting "false"
false responses

I don't see how that jibes with your original formula. If you want
something like your original formula, you might write simply:

=IF(MONTH('SH Harv Table'!E$4)=MONTH($G$82),Shrimp!$B$49,0)

However, in the previous paragraph, you wrote "month" __with_quotes__. I
presume your intent is that the dates in G82 and G83 might not be literally
the first and last dates of a month; instead, they might be any "monthly"
period (and perhaps not even that); for example, 5/16/2013 to 6/15/2013. In
that case, MONTH() will not work you for.

-----

Is this helpful?

-----

PS: In your original posting, you wrote the range "06/01/14-06/30/13"
[sic]. I interpreted that literally to mean a period that spanned a
__year__ or so. Now I wonder if "14" is a typo, and you meant 06/01/13.


----- original message -----

 

[joeu2004]

=IF('SH Harv Table'!$E$4>=$G$82,'SH Harv Table'!$E$4<=$G$83,
'SH Harv Table'!$F$4>=$G$82,'SH Harv Table'!$F$4<=$G$83,
'SH Harv Table'!$G$4>=$G$82,'SH Harv Table'!$G$4<=$G$83,
'SH Harv Table'!$H$4>=$G$82,'SH Harv Table'!$H$4<=$G$83,
'SH Harv Table'!$I$4>=$G$82,'SH Harv Table'!$I$4<=$G$83,
'SH Harv Table'!$J$4>=$G$82,'SH Harv Table'!$J$4<=$G$83,
'SH Harv Table'!$K$4>=$G$82,'SH Harv Table'!$K$4<=$G$83,
'SH Harv Table'!$L$4>=$G$82,'SH Harv Table'!$L$4<=$G$83,
'SH Harv Table'!$M$4>=$G$82,'SH Harv Table'!$M$4<=$G$83))))))))),
Shrimp!$B$49,0)

wherein I get a message "too many arguments for this function."

To explain the error....

The form of the IF() function is IF(condition, value_if_true,
value_if_false).
There are only 3 parameters.

What you posted is not even syntactically correct: too many right
parentheses. The Excel error is misleading because Excel is totally
confused. GIGO!

But if even you remove excess right parentheses, the result would have more
than one "condition" expression and more than 3 parameters. It is
meaningless.

See my previous response for an attempt to reasonably interpret your intent.

 

[JoyeNeedsHelp]

Thank you for your help!