Using the IF Function in EXCEL 1

[Llwynog]

Please help !
I've (crudely) started a table in Excel to record training/renewal dates, as below, with the 'Valid For (Years)' data selected from a drop down list of 1,2 or 3(Data/Validation/List).

C6 D6 E6
Date of Training Valid For (Years) Renewal Date
25-Oct-05 1 25-Oct-06


In the renewal date I've put in an IF statement
=IF(D6,1)*(C6+365) which gives the renewal date as 25-Oct-06.
Is there any way I can add =IF D6,2 etc in the same argument
or is there another way please ?
As you can guess, formulas are not my strong point!!
Thanks and regards,
Llwynog

 

[PHV]

You don't need IF.
Use a function to add [D6] years to [C6]
Just follow the function wizard.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TonyJollans]

Hi Llwynog,

As you can guess, formulas are not my strong point!!

Yes, indeed!!

What your formula does is return the product of ([red]*[/red] is the multiplication operator) [blue]IF(D6,1)[/blue] and [blue](C6+365)[/blue].

[blue]IF(D6,1)[/blue] means IF D6 is NOT FALSE then return 1, else return FALSE. As 1 (or 2 or 3) is not false it always returns 1 which always successfully multiplies by (C6+365) to give the date a year ahead of C6. If you had FALSE (or zero) in D6 you would see a different answer.

The format of an IF statement is:

[tt] IF([/tt][blue][tt]condition[/tt][/blue][tt],[/tt][green][tt]result_if_true[/tt][/green][tt],[/tt][red][tt]result_if_false[/tt][/red][tt])[/tt]

So you should have had:

[blue][tt] IF(D6=1,C6+365)[/tt][/blue]

Note that if you don't specify a result_if_false it defaults to FALSE.

As some years are leap years, it really would be more correct to add a year rather than 365 days and one way to do this is:

[blue][tt] =DATE(YEAR(C6)+1,MONTH(C6),DAY(C6))[/tt][/blue]

Now, functions can be nested. So, where you currently don't have anything if D6 is not equal to 1, you could include another IF function, this time checking for D6=2. This would give you:

[blue][tt] =IF(D6=1,DATE(YEAR(C6)+1,MONTH(C6),DAY(C6)),IF(D6=2,DATE(YEAR(C6)+2,MONTH(C6),DAY(C6))))[/tt][/blue]

With your data, however, it seems you just want to add the number of years from D6 to the date in C6. If this is so, the IF is more than you need and you can simplify it to:

[blue][tt] =DATE(YEAR(C6)+D6,MONTH(C6),DAY(C6))[/tt][/blue]

One final point. This forum is for VBA questions. Questions about Worksheet Functions are better asked in the Office Forum (Forum68)

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[