Hi,

I have col which contains year & month. Now i want to pull the values which
is less than or equal to current month.

A
2006-08
2008-04
2007-11
2008-01
2007-11

I tried with this formula but it is not working. Can any one help me?

=IF(year(a2)&"-"&MONTH(A2)<=year(TODAY()&"-"&month(today()),MONTH(TODAY())&"-"&YEAR(TODAY()),"")


- Christ

What is the format of cells in column A? Text or Date like "yyyy-mm"?
Stefi


€žChristopher Naveen€� ezt Ã*rta:

Hi,

I have col which contains year & month. Now i want to pull the values which
is less than or equal to current month.

A
2006-08
2008-04
2007-11
2008-01
2007-11

I tried with this formula but it is not working. Can any one help me?

=IF(year(a2)&"-"&MONTH(A2)<=year(TODAY()&"-"&month(today()),MONTH(TODAY())&"-"&YEAR(TODAY()),"")


- Christ

=IF(A2<=TEXT(TODAY(),"yyyy-mm"),A2,"") if the column A entries are text.

=IF(TEXT(A2,"yyyy-mm")<=TEXT(TODAY(),"yyyy-mm"),A2,"") if column A entries
are dates formatted that way (and it would still work with text in A). If
you do have dates in A, you'd presumably want to format your result cells to
show the date the same way.

It isn't entrirely clear what you want as your output. From your
description I assumed that you wanted to see the column A values carried
forward if they met your criteria, but from your attempted formula it looks
as if you might want today's date formatted in a different way. If need be
you can replace the A2 in the output by whatever you do want, either a date
which you can format in the cell or a text output.

With your formula, you need to think carefully about the syntax for each
function you are using, and make sure that the right parameters fall in the
right places compared with your parentheses and the commas. For example,
your second YEAR function is trying to work on a text string, whereas you
probably wanted it to work on a date TODAY(). At first glance it doesn't
even look as if you've got the same number of opening and closing
parentheses in the equation.
--
David Biddulph

"Christopher Naveen" wrote in
message ...
Hi,

I have col which contains year & month. Now i want to pull the values
which
is less than or equal to current month.

A
2006-08
2008-04
2007-11
2008-01
2007-11

I tried with this formula but it is not working. Can any one help me?

=IF(year(a2)&"-"&MONTH(A2)<=year(TODAY()&"-"&month(today()),MONTH(TODAY())&"-"&YEAR(TODAY()),"")


- Christ

Hi David,

Thank u very much!!!!!!. Yes it is working now.

-Christ

"David Biddulph" wrote:

=IF(A2<=TEXT(TODAY(),"yyyy-mm"),A2,"") if the column A entries are text.

=IF(TEXT(A2,"yyyy-mm")<=TEXT(TODAY(),"yyyy-mm"),A2,"") if column A entries
are dates formatted that way (and it would still work with text in A). If
you do have dates in A, you'd presumably want to format your result cells to
show the date the same way.

It isn't entrirely clear what you want as your output. From your
description I assumed that you wanted to see the column A values carried
forward if they met your criteria, but from your attempted formula it looks
as if you might want today's date formatted in a different way. If need be
you can replace the A2 in the output by whatever you do want, either a date
which you can format in the cell or a text output.

With your formula, you need to think carefully about the syntax for each
function you are using, and make sure that the right parameters fall in the
right places compared with your parentheses and the commas. For example,
your second YEAR function is trying to work on a text string, whereas you
probably wanted it to work on a date TODAY(). At first glance it doesn't
even look as if you've got the same number of opening and closing
parentheses in the equation.
--
David Biddulph

"Christopher Naveen" wrote in
message ...
Hi,

I have col which contains year & month. Now i want to pull the values
which
is less than or equal to current month.

A
2006-08
2008-04
2007-11
2008-01
2007-11

I tried with this formula but it is not working. Can any one help me?

=IF(year(a2)&"-"&MONTH(A2)<=year(TODAY()&"-"&month(today()),MONTH(TODAY())&"-"&YEAR(TODAY()),"")


- Christ