Missing "Need"

A

AlanStotty

Hi,

I am using the following calculation in the field:

=IF($AG120 >=R$4, "", IF($AH120 <=R$4, "", "need"))

where AG120 = 23/01/2007, R4 = 01/01/2007 and AH120 = 02/09/2007.

But it does not seem to populate with "need".

Any ideas?
 
B

bj

Ag120 is greater than r4 ithe cell should populate with ""
what do you want it to do?
 
V

Vergel Adriano

Because the date in AG120 is greater than the date in R4. Try

=IF($AG120 <=R$4, "", IF($AH120 <=R$4, "", "need"))
 
A

AlanStotty

Ag120 is greater than r4 ithe cell should populate with ""
what do you want it to do?










- Show quoted text -
Click to expand...

I want it to populate with "need" as it is within that month. Hope
that is clearer. :)
 
D

David Biddulph

The formula is doing what you've asked it to do.

23/01/2007 >= 01/01/2007
$AG120 >=R$4
hence you get "" from the first IF() function, and you don't get as far as
the alternative path to the second IF().
 
D

David Biddulph

No. Not any clearer.

I'm not sure which cell you are looking for being in the same month as which
other cell, but try
=IF(MONTH(ref1)=MONTH(ref2),"need"...

If you want to to be same month and year, then
=IF(AND(MONTH(ref1)=MONTH(ref2),YEAR(ref1)=YEAR(ref2)),"need"...
 
B

bj

not really September 2 is not in the same month as January 1
So I am more confused as to what you need.

there are four options
A AG120>=r4,AH120<=r4
B AG120<r4,AH120<=r4
C AG120>=r4,AH120>r4
D AG120<r4,AH120>r4


with your current equation, only D would give a "Need" response
the others would give "".
your data would be in option C

whatr response do you need for each of the options?
and please explain more about your "within the month" comment
 
A

AlanStotty

not really September 2 is not in the same month as January 1
So I am more confused as to what you need.

there are four options
A AG120>=r4,AH120<=r4
B AG120<r4,AH120<=r4
C AG120>=r4,AH120>r4
D AG120<r4,AH120>r4

with your current equation, only D would give a "Need" response
the others would give "".
your data would be in option C

whatr response do you need for each of the options?
and please explain more about your "within the month" comment






- Show quoted text -
Click to expand...



I have put an example of what I want:


01/01/2007 01/02/2007 01/03/2007 01/04/2007
01/05/2007 FromDate ToDate
need need
need 12/02/2207
15/04/2007


As you can see if the date is within the month, I want to put need
otherwise leave blank. Hope this is clearrrrrrrrerrrrrr. :)
 
C

Cortez

Are you sure that this isn't what you need?

=IF($AG120 >=R$4, IF($AH120 <=R$4, "", "need"),"")

Not certain what you are trying but to me this makes more sense.

TK
 
B

bj

Since the format of your data got lost in the respose
I assume (shudder) that
12/02/2007 (Not 2207) is in AG120
15/04/2007 is in AH120
and that the three needs are under the 01/02, 01/03, 01/04 in row 4

that even though the 01/02 is less than the 12/02 you still want the "Need"
try
=if(and(R$4>=date(year($AG120),month($AG120),1),R$4<=date(year($AH120),month($AH120)+1,0)),"need","")

If I am wrong and you want onlt those actually between the two dates try
=if(and(R$4>=$AG120,R$4<=$AH120),"need","")
 
B

bj

additionally I think your greater than and less than signed are reversed from
what they need to be in your original equation If you want what I think you
want. in other words your original equation might work with them reversed.
 
D

David Biddulph

From your Google spreadsheets link below:
"You can no longer view this document. The owner of the spreadsheet has
changed the sharing options."

Perhaps you can try an Excel group in your own language, as it's obviously
difficult for you to explain in English what you're trying to do.
 
A

AlanStotty

Since the format of your data got lost in the respose
I assume (shudder) that
12/02/2007 (Not 2207) is in AG120
15/04/2007 is in AH120
and that the three needs are under the 01/02, 01/03, 01/04 in row 4

that even though the 01/02 is less than the 12/02 you still want the "Need"
try
=if(and(R$4>=date(year($AG120),month($AG120),1),R$4<=date(year($AH120),mont­h($AH120)+1,0)),"need","")

If I am wrong and you want onlt those actually between the two dates try
=if(and(R$4>=$AG120,R$4<=$AH120),"need","")








- Show quoted text -
Click to expand...

That worked a treat. Very grateful.
 
Top