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?

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

"AlanStotty" wrote:

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?

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

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



"AlanStotty" wrote:

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?

On Mar 5, 2:16 pm, bj wrote:
Ag120 is greater than r4 ithe cell should populate with ""
what do you want it to do?



"AlanStotty" wrote:
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?- Hide quoted text -

- Show quoted text -

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

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().
--
David Biddulph

"AlanStotty" wrote in message
oups.com...
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?

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(re f2)),"need"...
--
David Biddulph

"AlanStotty" wrote in message
oups.com...

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

On Mar 5, 2:16 pm, bj wrote:
Ag120 is greater than r4 ithe cell should populate with ""
what do you want it to do?

"AlanStotty" wrote:
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?-

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,AH120r4
D AG120<r4,AH120r4


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

"AlanStotty" wrote:

On Mar 5, 2:16 pm, bj wrote:
Ag120 is greater than r4 ithe cell should populate with ""
what do you want it to do?



"AlanStotty" wrote:
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?- Hide quoted text -

- Show quoted text -

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

On Mar 5, 3:00 pm, bj wrote:
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,AH120r4
D AG120<r4,AH120r4

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



"AlanStotty" wrote:
On Mar 5, 2:16 pm, bj wrote:
Ag120 is greater than r4 ithe cell should populate with ""
what do you want it to do?

"AlanStotty" wrote:
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?- Hide quoted text -

- Show quoted text -

I want it to populate with "need" as it is within that month. Hope
that is clearer. :-)- Hide quoted text -

- Show quoted text -



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. :-)

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


On Mar 5, 8:32 am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:
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().
--
David Biddulph

"AlanStotty" wrote in message

oups.com...

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?

On Mar 5, 3:59 pm, "Cortez" wrote:
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

On Mar 5, 8:32 am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:



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().
--
David Biddulph

"AlanStotty" wrote in message

roups.com...

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?- Hide quoted text -

- Show quoted text -

Try this link for example, it should open an excel file. Shows you
what I want, just need the formula so it can work it out itself.
Thanks for bearing with me.

http://spreadsheets.google.com/fm?ke...3497&f mcmd=4

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","")


"AlanStotty" wrote:

On Mar 5, 3:00 pm, bj wrote:
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,AH120r4
D AG120<r4,AH120r4

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



"AlanStotty" wrote:
On Mar 5, 2:16 pm, bj wrote:
Ag120 is greater than r4 ithe cell should populate with ""
what do you want it to do?

"AlanStotty" wrote:
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?- Hide quoted text -

- Show quoted text -

I want it to populate with "need" as it is within that month. Hope
that is clearer. :-)- Hide quoted text -

- Show quoted text -



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. :-)

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.

"bj" wrote:

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","")


"AlanStotty" wrote:

On Mar 5, 3:00 pm, bj wrote:
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,AH120r4
D AG120<r4,AH120r4

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



"AlanStotty" wrote:
On Mar 5, 2:16 pm, bj wrote:
Ag120 is greater than r4 ithe cell should populate with ""
what do you want it to do?

"AlanStotty" wrote:
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?- Hide quoted text -

- Show quoted text -

I want it to populate with "need" as it is within that month. Hope
that is clearer. :-)- Hide quoted text -

- Show quoted text -



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. :-)

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.
--
David Biddulph

"AlanStotty" wrote in message
oups.com...

Try this link for example, it should open an excel file. Shows you
what I want, just need the formula so it can work it out itself.
Thanks for bearing with me.

http://spreadsheets.google.com/fm?ke...3497&f mcmd=4

On Mar 5, 3:59 pm, "Cortez" wrote:
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

On Mar 5, 8:32 am, "David Biddulph" <groups [at] biddulph.org.uk
wrote:



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().
--
David Biddulph

"AlanStotty" wrote in message

roups.com...

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?-

On Mar 5, 6:12 pm, bj wrote:
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","")



"AlanStotty" wrote:
On Mar 5, 3:00 pm, bj wrote:
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,AH120r4
D AG120<r4,AH120r4

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

"AlanStotty" wrote:
On Mar 5, 2:16 pm, bj wrote:
Ag120 is greater than r4 ithe cell should populate with ""
what do you want it to do?

"AlanStotty" wrote:
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?- Hide quoted text -

- Show quoted text -

I want it to populate with "need" as it is within that month. Hope
that is clearer. :-)- Hide quoted text -

- Show quoted text -

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. :-)- Hide quoted text -

- Show quoted text -

That worked a treat. Very grateful.