Conditional Formula w/Date=Where did I go wrong?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This seemed simple. I need to determine if a date is in the fiscal year, and
if so, show data in a seperate column. (i.e. if a date in column a is
between x and y, show the amount in column b in column c, or if no, show
nothing)

I thought this would work, but no go.

=IF(AND(C16>=7/1/2007,C16<=6/31/2008),E16,"")

Where did I go wrong?

Thanks
 
Thanks for the fast response Mama, but that returned a #VALUE!

My cell values are as follows: c16 is 1/27/2008, E16 is 2,025.00; there are
no formulas in those cells.
 
Always best to stick to non-ambiguous dates

=IF(AND(C16>=--"2007-07-01",C16<=--"2008-06-31"),E16,"")

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Thanks Bob, but unfortunately, still no go.

Bob Phillips said:
Always best to stick to non-ambiguous dates

=IF(AND(C16>=--"2007-07-01",C16<=--"2008-06-31"),E16,"")

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
Click to expand...
 
It's because you have a non existent date as your second criteria, Change it
to --"2008-06-30"

AFAIK there will never be a 06/31/08
 
Check and make sure your date is really a true Excel date:

=ISNUMBER(C16)

That should return TRUE.
 
Like I said; it looks simple, but... Still #VALUE! I checked the formatting
on the subject cells. Nothing that shouldn't be there. C16 is formatted as
a date and E16 is just a monetary value.

Thanks for trying BJ.
 
I believe it his 2nd criteria using a non existent date which will default
to a text string
 
Mia culpa,
Part of the problem may be that I forgot the Quotation marks
=IF(AND(C16>=datevalue("2007-07-01"),C16<=datevalue("2008-06-31")),E16,"")

If this doesn't work/
if you change the format for the c16, does the cell change?
in other words is it text or a date.

if c16 is text, what happens if you type =c16="what it looks like"?
if c16 is brought in through a macro or copied in, it may be the text with
spaces or non printed characters
 
Thanks all. I hate (and yet should always expect) the answers that are right
in front of me. Another monumental 'Duh' moment.

Here's the one that worked:

=IF(AND(C16>=--"2007-07-01",C16<=--"2008-06-30"),E16,"")
 
Back
Top