date logic
 

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

date logic
 

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

if this helps please click yes thanks

"comfuted" wrote:

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

date logic
 

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

"Eduardo" wrote:

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

if this helps please click yes thanks

"comfuted" wrote:

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

date logic
 

One way:

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



comfuted wrote:

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

--

Dave Peterson

date logic
 

On Mon, 31 Aug 2009 12:07:01 -0700, comfuted
wrote:

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

date logic
 

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

"comfuted" wrote:

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

"Eduardo" wrote:

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

if this helps please click yes thanks

"comfuted" wrote:

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

date logic
 

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

"Dave Peterson" wrote:

One way:

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



comfuted wrote:

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

--

Dave Peterson

date logic
 

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

"Ron Rosenfeld" wrote:

On Mon, 31 Aug 2009 12:07:01 -0700, comfuted
wrote:

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

date logic
 

It worked ok for me.

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

comfuted wrote:

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

"Dave Peterson" wrote:

One way:

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



comfuted wrote:

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

--

Dave Peterson


--

Dave Peterson

date logic
 

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

"Dave Peterson" wrote:

It worked ok for me.

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

comfuted wrote:

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

"Dave Peterson" wrote:

One way:

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



comfuted wrote:

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

--

Dave Peterson


--

Dave Peterson

date logic
 

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

comfuted wrote:

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

"Dave Peterson" wrote:

It worked ok for me.

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

comfuted wrote:

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

"Dave Peterson" wrote:

One way:

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



comfuted wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

date logic
 

On Mon, 31 Aug 2009 13:43:01 -0700, comfuted
wrote:

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

date logic
 

"Dave Peterson" wrote:

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

comfuted wrote:

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

"Dave Peterson" wrote:

It worked ok for me.

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

comfuted wrote:

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

"Dave Peterson" wrote:

One way:

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



comfuted wrote:

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

--

Dave Peterson


--

Dave Peterson


--

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

date logic
 

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?

comfuted wrote:

"Dave Peterson" wrote:
<<snipped
Dave:
Thanks again. A1 was formatted as date, I did it again just to make sure.
isnumber(A1) results TRUE
Norman

--

Dave Peterson