date logic

[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]

Hi
=if(or(A1>=1/1/2009.a1<4/1/2009),b1,0)

if this helps please click yes thanks

 

[comfuted]

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

 

[Dave Peterson]

One way:

=if(and(date(2009,1,1)<=a1,a1<date(2009,4,1)),b1,0)

 

[Ron Rosenfeld]

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?

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

 

[Sean Timmons]

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

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

 

[comfuted]

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

 

[comfuted]

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

 

[Dave Peterson]

It worked ok for me.

I'm guessing that you changed the formula--or the value in A1 isn't really a
date.

 

[comfuted]

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

 

[comfuted]

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

 

[Dave Peterson]

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

 

[Ron Rosenfeld]

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

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

 

[comfuted]

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!)

Thanks again. A1 was formatted as date, I did it again just to make sure.
isnumber(A1) results TRUE
Norman

 

[Dave Peterson]

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?