ExcelBanter - date formula

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - date formula (https://www.excelbanter.com/excel-discussion-misc-queries/136407-date-formula.html)

I'm having data with date of commissionings. I want to change the data as,
"01/04/2005" before commissioned as 1, and "01/04/2005" after commissioned as
2,"01/04/2006" after commissioned as 3.Like that I want to change my data. I
used formula
=IF(DATEVALUE(F3)<1/4/2005,1,IF(DATEVALUE(F3)=1/4/2005,2,IF(DATEVALUE(F3)=1/4/2006,3)))
but it showing 2 at the date 31/01/2005 actually it has
to show 1.why?
Is there any mistake in formula ?
can any one help on this? very necessary

thanks for cooperation

"Toppers" wrote:

If F3 is Date format:

=IF(F3=DATEVALUE("1/4/2006"),3,IF(F3=DATEVALUE("1/4/2005"),2,1))

NOTE change of order of testing dates.

"deepak bsg" wrote:

I'm having data with date of commissionings. I want to change the data as,
"01/04/2005" before commissioned as 1, and "01/04/2005" after commissioned as
2,"01/04/2006" after commissioned as 3.Like that I want to change my data. I
used formula
=IF(DATEVALUE(F3)<1/4/2005,1,IF(DATEVALUE(F3)=1/4/2005,2,IF(DATEVALUE(F3)=1/4/2006,3)))
but it showing 2 at the date 31/01/2005 actually it has
to show 1.why?
Is there any mistake in formula ?
can any one help on this? very necessary