How do I write an IF function using dates?

S

Stinky

I'm trying to do a logical test that uses dates. If I use > it returns the
correct result, but if I use = or < I get the wrong result:

A B C
1 25/03/2010 500 0
2 24/03/2010 300 0
3 27/03/2010 750 750

In C1 the formula is =IF(A1=25/3/2010,K32,0) which I would expect to be true
and return 500, but it returns the false value
In C2 the formula is =IF(A2<25/3/2010,K33,0) which I would expect to be true
and return 300, but it returns the false value
In C3 the formula is =IF(A3>25/3/2010,K34,0) which is true and returns the
correct value.

Can anyone help?
 
R

Rick Rothstein

Excel sees 25/3/2010 as a multiple division, not a date. Use either this...

--"25/03/2010" (Note the double minus sign in front)

or this...

DATE(2010,3,25)
 
R

RonaldoOneNil

=IF(DATEVALUE(TEXT(A1,"dd/mm/yyyy"))=DATEVALUE("25/3/2010"),K32,0)
=IF(DATEVALUE(TEXT(A2,"dd/mm/yyyy"))<DATEVALUE("25/3/2010"),K33,0)
=IF(DATEVALUE(TEXT(A3,"dd/mm/yyyy"))>DATEVALUE("25/3/2010"),K34,0)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multiple variables to sort and sum, return values<0 with sum refer 5
Lookup date between 2 dates 1
DateRange Lookup no exact match, can't use lesser 4
if formula results in False - can you help? 1
Counting cells in a selectable date range 0
How do I not count cells that have formulas in them 2
How to add data from cells based on different criterias? 2
IF Function with Dates 9

Top