date logic

  • Thread starter Thread starter comfuted
  • Start date Start date
C

comfuted

I want to test if a date is in first quarter, and if so, place value of a
cell into another. Otherwise, make it zero. A1 has date, column B1 has
value. In C1 I put:
=if(1/1/2009<=a1<4/1/2009,b1,0). This doesn't work, always says 0. What am I
doing wrong?
Thanks
 
Eduardo:
That did not work. I even simplified it to test where the problem was:
=if(a1<4/1/2009,b1,0) and it did not work. Date in a1 was 3/1/2009
 
I want to test if a date is in first quarter, and if so, place value of a
cell into another. Otherwise, make it zero. A1 has date, column B1 has
value. In C1 I put:
=if(1/1/2009<=a1<4/1/2009,b1,0). This doesn't work, always says 0. What am I
doing wrong?
Click to expand...

1. The syntax of your logical statement is incorrect.
2. The construct 1/1/2009 does not represent a date. It represents one divided
by 2009 or 0.00049776.

If you want to see if the date in A1 is in the first quarter, try:

C1: =IF(MONTH(A1)<=3,B1,0)

--ron
 
=if(month(a1)/3<=1,B1,0)

comfuted said:
Eduardo:
That did not work. I even simplified it to test where the problem was:
=if(a1<4/1/2009,b1,0) and it did not work. Date in a1 was 3/1/2009
Click to expand...
 
Dave:
That did not work. I also tried simplifying it to
=if(date(2009,1,1)<a1,b1,0) and that didn't work either
thanks
 
Thanks, Ron.
That is a simple way to solve the quarter issue, but what if I had some
arbitrary dates that did not coincide with end of month? The Date function
suggested by Dave did not seem to work. Is there a way to represent a date so
logical comparisons will work properly?
Thanks
Norman
 
It worked ok for me.

I'm guessing that you changed the formula--or the value in A1 isn't really a
date.
 
Dave:
I copied and pasted your formula directly into C1. I put 3/1/2009 into A1
and 2 into B1. Result in C1 is 0. I tested the date by putting
=a1+1 into D1, result 3/2/2009, which seems to me indicates it is a date.
Maybe there is something about this spreadsheet that is wierd?
Norman
 
Barry:
I used the formula as-is, and I also did it like this:
A B C
3/1/2009 2 =IF(A1<DATE(2009,4,1),B1,0)
result is 0
Thanks
 
This isn't a good test. Excel will coerce anything that looks like a number
(that includes a date, too!) to a number when you do an arithmetic operation
with it.

You could try reformatting the cell (A1) to see if it changes format.

Or you could use a formula in a different cell: =isnumber(a1)

I'm betting that that cell is formatted as text.

Try reformatting it as General (or a date) and then reenter the value into that
cell.

(and make sure that calculation is set to automatic, too!)
Dave:
I copied and pasted your formula directly into C1. I put 3/1/2009 into A1
and 2 into B1. Result in C1 is 0. I tested the date by putting
=a1+1 into D1, result 3/2/2009, which seems to me indicates it is a date.
Maybe there is something about this spreadsheet that is wierd?
Norman
Click to expand...
 
Thanks, Ron.
That is a simple way to solve the quarter issue, but what if I had some
arbitrary dates that did not coincide with end of month? The Date function
suggested by Dave did not seem to work. Is there a way to represent a date so
logical comparisons will work properly?
Thanks
Norman
Click to expand...

If you used Dave's formula, then most likely your apparent date in A1 is text
and not a real date.

Was the value in A1 entered directly into the cell, or is it the result of a
formula?

If it is the result of a formula, please post the formula.

If you entered the value directly into A1, do this again BUT be sure the cell
is NOT formatted as TEXT at the time you enter the value. In other words,
first change the format to something like General; and then enter the date.

--ron
 
Dave Peterson said:
This isn't a good test. Excel will coerce anything that looks like a number
(that includes a date, too!) to a number when you do an arithmetic operation
with it.

You could try reformatting the cell (A1) to see if it changes format.

Or you could use a formula in a different cell: =isnumber(a1)

I'm betting that that cell is formatted as text.

Try reformatting it as General (or a date) and then reenter the value into that
cell.

(and make sure that calculation is set to automatic, too!)
Click to expand...
Thanks again. A1 was formatted as date, I did it again just to make sure.
isnumber(A1) results TRUE
Norman
 
The formula worked for me.

Maybe you pointed at the wrong address???

If you reformat the cell to an unambiguous date format:
mmmm dd, yyyy

Does the display (not the formulabar) change?
 
Back
Top