How do I write an IF function using dates?
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(A325/3/2010,K34,0) which is true and returns the correct value. Can anyone help? |
How do I write an IF function using dates?
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) -- Rick (MVP - Excel) "Stinky" wrote in message ... 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(A325/3/2010,K34,0) which is true and returns the correct value. Can anyone help? |
How do I write an IF function using dates?
=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) "Stinky" wrote: 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(A325/3/2010,K34,0) which is true and returns the correct value. Can anyone help? |
How do I write an IF function using dates?
You're comparing with 25 divided by 3 and then divided by 2010.
Others have suggested --"25/3/2010" or DATEVALUE, but if you have a date like 4/5/2010 you have an ambiguity as to whether this is 4 May or 5 April, and this is determined by your Windows Regional Options (in Control Panel, not in Excel), so you're better with an unambiguous definition DATE(2010,3,25). -- David Biddulph "Stinky" wrote in message ... 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(A325/3/2010,K34,0) which is true and returns the correct value. Can anyone help? |
All times are GMT +1. The time now is 06:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com