how to use if and date function to return certain values

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Hint: Try:
=IF(YEAR(B1)-YEAR(A1) < or > or = to something then post your workbook (with formula)....

hi rajuj. so column B is a repeat of Today's date? can you just place a TODAY() formula in 1 single cell & make them all use that? TODAY is volatile, so Excel calculates it constantly. having so many cells with that is not good. say i put it in E1. also, you mentioned:

do , if b-a is more than 2 years and less than 5 years, say 40
do , if b-a is less than 2 years , say 60 otherwise blank

- what about 2 years? there's more than 2 & less than 2.
- why is there an "otherwise"? what other options are there? 0 is less than 2 years, so it'll return 60. anything more than 5 is 20.

maybe:
=LOOKUP(DATEDIF(A2,$E$1,"y"),{0,3,6},{20,40,60})
change the parts in red to the minimum years for the numbers to appear. so i'm assuming 0 years get 20, 3 years 40, 6 years 60.

if the "otherwise" means Column A being blank or a text, then:
=IF(ISNUMBER(A2),LOOKUP(DATEDIF(A2,$E$1,"y"),{0,3,6},{20,40,60}),"")

I am enclosing the worksheet , please let me know, if it is for numbers, then it is working, if it is date, that is the difference is in date, to be exact date between the two dates, like the actual days of 30,31 days, then what is the formula,I should use.
There is a formula days360, but a year has 365 or 366 days.
What is datedif function, and how to use in this regard.
Could not i use the if function , as i normally use for nested if function.

DATEDIF(start date,end date,"y") give the exact different years btw two dates (i.e 2/1/2014 and 1/1/2016 = 1 year 364 days => 1 year)
So you have:
=IFERROR(LOOKUP(DATEDIF(A1,B1,"y"),{0,2,5},{60,40,20}),"")

rajuh,

Your formula
=IF(DATEDIF(A1,B1,"D")>1825,120),IF(DATEDIF(A1,B1,"D")<1825,80)
must be corrected be remove the bracket into the end:

=IF(DATEDIF(A1,B1,"D")>1825,120,IF(DATEDIF(A1,B1,"D")<1825,80))

About IFERROR,
IFERROR(A,B) returns B IF A is ERROR, of not, A.

Thankkkkk you very much. I should not have bracketed.Hi beboo, will it be correct to the exact days, considering the actual dates falling in future months and years in my formula.
I have given days, instead of year,month, and days.
For academic interest, how to substitute these days in years, In excel 5 will not denote 5 years. Is there any other way.
in your formula 0,2,5 has been entered as array formula.
Is my formula, correct? after your correction. I think, that actual days may be more or less than actual 365. How to correct , because, any day if entered in a1 should work.
I think your first formula with modification of 0,2,5 would be exact. pl expecting reply