I have data with date of commissionings of three finantial years. I want to
put the numbers instead of dates ex:01/04/2004 to 01/03/2005 (in between
these years) as 1 and 01/04/2005 to 01/03/2006 as 2 and 01/04/2006 up to
current as 3. I have many different dates in between these years, already I
use IF & datevalue functions. but I'm not getting result accurately.
NOTE : There are some d.o.comm are there before 01/04/2004 also they are
also comes under 1 category.
Kindly give any assistiance please as soon as possible.

Try:
=IF(A1<=DATEVALUE("01/03/05"),1,IF(AND(A1=DATEVALUE("01/04/05"),A1<=DATEVALUE("01/03/06")),2,3))

What happens to dates from March 2nd to March 31st?

HTH

"deepak bsg" wrote:

I have data with date of commissionings of three finantial years. I want to
put the numbers instead of dates ex:01/04/2004 to 01/03/2005 (in between
these years) as 1 and 01/04/2005 to 01/03/2006 as 2 and 01/04/2006 up to
current as 3. I have many different dates in between these years, already I
use IF & datevalue functions. but I'm not getting result accurately.
NOTE : There are some d.o.comm are there before 01/04/2004 also they are
also comes under 1 category.
Kindly give any assistiance please as soon as possible.

Thanks for prompt reply, already I used the formula what u have specified(But
I tried u r formula also). When I'm using the formula it is not satisfying
the condition i.e ex: 31/10/2004 has to show in 2 according to our formula
but it is showing 1. & for this date also 30/06/2005 has to show 2, but it is
showing 3.
assume & help me please as early as possible.

"Toppers" wrote:

Try:
=IF(A1<=DATEVALUE("01/03/05"),1,IF(AND(A1=DATEVALUE("01/04/05"),A1<=DATEVALUE("01/03/06")),2,3))

What happens to dates from March 2nd to March 31st?

HTH

"deepak bsg" wrote:

I have data with date of commissionings of three finantial years. I want to
put the numbers instead of dates ex:01/04/2004 to 01/03/2005 (in between
these years) as 1 and 01/04/2005 to 01/03/2006 as 2 and 01/04/2006 up to
current as 3. I have many different dates in between these years, already I
use IF & datevalue functions. but I'm not getting result accurately.
NOTE : There are some d.o.comm are there before 01/04/2004 also they are
also comes under 1 category.
Kindly give any assistiance please as soon as possible.

31/10/04 is betwen 01/04/04 amd 01/03/05 therefore is 1 not 2!

30/06/05 shows 2 in my worksheet!

Check your data.

"deepak bsg" wrote:

Thanks for prompt reply, already I used the formula what u have specified(But
I tried u r formula also). When I'm using the formula it is not satisfying
the condition i.e ex: 31/10/2004 has to show in 2 according to our formula
but it is showing 1. & for this date also 30/06/2005 has to show 2, but it is
showing 3.
assume & help me please as early as possible.

"Toppers" wrote:

Try:
=IF(A1<=DATEVALUE("01/03/05"),1,IF(AND(A1=DATEVALUE("01/04/05"),A1<=DATEVALUE("01/03/06")),2,3))

What happens to dates from March 2nd to March 31st?

HTH

"deepak bsg" wrote:

I have data with date of commissionings of three finantial years. I want to
put the numbers instead of dates ex:01/04/2004 to 01/03/2005 (in between
these years) as 1 and 01/04/2005 to 01/03/2006 as 2 and 01/04/2006 up to
current as 3. I have many different dates in between these years, already I
use IF & datevalue functions. but I'm not getting result accurately.
NOTE : There are some d.o.comm are there before 01/04/2004 also they are
also comes under 1 category.
Kindly give any assistiance please as soon as possible.