Excel formula from looking the base date and convert into desire result

[PRADEEPB270]

I have an excel file.I want to solve the category condition from an excel formula.In this file,G3 will be my base date and I want the excel formula in colum no.I i.e.Category.For this,look the base date i.e.G3 and compare to column F i.e.Date Column of each material code.
For more clarification,please refer the attach file

[oeldere]

See the green cells.

I divided by 365.

This can give you an wrong answer is there are years with 366 days and you want an exact match (on a date) e.g. 31.01-2008 - 31.01.2013.

[newdoverman]

There is a problem with the dates that you are using in that they are not in a format that is recognized by Excel. Change the dates to an Excel acceptable date format then date formulae will work.

After changing the dates to acceptable date formats enter this formula in I5 and copy down the column.

=IF(DATEDIF(F5,$G$3,"Y")>4,">4 Years",IF(DATEDIF(F5,$G$3,"Y")>=3,">3 Years <4 Years",IF(DATEDIF(F5,$G$3,"Y")>=2,">2 Years <3 Years",IF(DATEDIF(F5,$G$3,"Y")>=1,">1 Year <2 Years",IF(DATEDIF(F5,$G$3,"Y")<1,"<1 Year","")))))

[PRADEEPB270]

Thank you sir for your nice formula provide to me.

[bebo021999]

Your dates are not in correct format. If possible, changing the format would better for shortern the formula.
Anyway, if impossible, try this without changing the format:

Please Login or Register  to view this content.