Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"AND FUNCTION" RETURNS FALSE WHEN CRITERIA IS TRUELY MET. HELP!!!
I have a problem using AND function on date data.
In cell A2, I have date date "10/1/2009". In cell A3, I have AND fuction as =AND(A2=10/1/2009, A2<=10/24/2009). This should return a TRUE but instead returns FALSE. Why? Thank you, BB |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"AND FUNCTION" RETURNS FALSE WHEN CRITERIA IS TRUELY MET. HELP!!!
The issue is how you expressed your dates
Try =AND(A2=DATE(2009,10,1),A2<=DATE(2009,10,24)) As written Excel does not recognize 10/1/2009 as a date, it does the math 10 divided by 1 divided by 2009 -- If this helps, please remember to click yes. "gamn" wrote: I have a problem using AND function on date data. In cell A2, I have date date "10/1/2009". In cell A3, I have AND fuction as =AND(A2=10/1/2009, A2<=10/24/2009). This should return a TRUE but instead returns FALSE. Why? Thank you, BB |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"AND FUNCTION" RETURNS FALSE WHEN CRITERIA IS TRUELY MET. HELP!!!
On Tue, 23 Mar 2010 13:47:01 -0700, gamn
wrote: In cell A2, I have date date "10/1/2009". In cell A3, I have AND fuction as =AND(A2=10/1/2009, A2<=10/24/2009). This should return a TRUE but instead returns FALSE. Why? because 10/1/2009 is not the same as "10/1/2009" The latter, in A2, is interpreted as a date and stored as an integer probably equal to 40087, depending on the date system you are using in Excel. The former: 10/1/2009 is computed as 10÷1÷2009 or 0.004978. To unambiguously enter a date into your formula, you should either use a cell reference containing the date, or use the DATE function. eg: =and(a2=date(2009,10,1),a2<=date(2009,10,24)) --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"AND FUNCTION" RETURNS FALSE WHEN CRITERIA IS TRUELY MET. HEL
I tried that but still a NO-GO. See link for a screen shot.
https://emerson.sendthisfile.com/Gvn...YGVN1QSDhpQeoc Thank you, BB "Paul C" wrote: The issue is how you expressed your dates Try =AND(A2=DATE(2009,10,1),A2<=DATE(2009,10,24)) As written Excel does not recognize 10/1/2009 as a date, it does the math 10 divided by 1 divided by 2009 -- If this helps, please remember to click yes. "gamn" wrote: I have a problem using AND function on date data. In cell A2, I have date date "10/1/2009". In cell A3, I have AND fuction as =AND(A2=10/1/2009, A2<=10/24/2009). This should return a TRUE but instead returns FALSE. Why? Thank you, BB |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"AND FUNCTION" RETURNS FALSE WHEN CRITERIA IS TRUELY MET. HEL
As explained by Paul, the order of parameters for the Date function is:
year, month, day. Regards, Fred "gamn" wrote in message ... I tried that but still a NO-GO. See link for a screen shot. https://emerson.sendthisfile.com/Gvn...YGVN1QSDhpQeoc Thank you, BB "Paul C" wrote: The issue is how you expressed your dates Try =AND(A2=DATE(2009,10,1),A2<=DATE(2009,10,24)) As written Excel does not recognize 10/1/2009 as a date, it does the math 10 divided by 1 divided by 2009 -- If this helps, please remember to click yes. "gamn" wrote: I have a problem using AND function on date data. In cell A2, I have date date "10/1/2009". In cell A3, I have AND fuction as =AND(A2=10/1/2009, A2<=10/24/2009). This should return a TRUE but instead returns FALSE. Why? Thank you, BB |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"AND FUNCTION" RETURNS FALSE WHEN CRITERIA IS TRUELY MET. HEL
But you did not follow Paul's advice! The syntax of the DATE function is
DATE(year, month, day). This is totally independent of you preferred data format. In the screen capture you linked us to you have DATE(10,1,2009) when you need DATE(2009,10,1) best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "gamn" wrote in message ... I tried that but still a NO-GO. See link for a screen shot. https://emerson.sendthisfile.com/Gvn...YGVN1QSDhpQeoc Thank you, BB "Paul C" wrote: The issue is how you expressed your dates Try =AND(A2=DATE(2009,10,1),A2<=DATE(2009,10,24)) As written Excel does not recognize 10/1/2009 as a date, it does the math 10 divided by 1 divided by 2009 -- If this helps, please remember to click yes. "gamn" wrote: I have a problem using AND function on date data. In cell A2, I have date date "10/1/2009". In cell A3, I have AND fuction as =AND(A2=10/1/2009, A2<=10/24/2009). This should return a TRUE but instead returns FALSE. Why? Thank you, BB |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel crashes when typing "false" in VLookup function | Excel Worksheet Functions | |||
=IF(test,true,false) only ever returns "true"? | Excel Worksheet Functions | |||
FIND function, when FALSE how can you return nothing (" ") instead of#VALUE or #NAME | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |