Dates in IF Functions

[sprog]

Hi there

I am trying write an IF function that will calculate the number of years between a particular date and an employee's start date. If they have been employed for 2+ years then I want "Yes" to appear.

I have put =IF((01/05/05 - C6)>=2, "Yes", "No")
C6 being the cell with the start date in it. My query is returning "No" for everything - when some should be "Yes". I think there is a problem with the date, as the query itself is returning something.

Any suggestions would be greatly appreciated.

Thanks

 

[macropod]

hi sprog,

Try:
=IF(DATEVALUE("01/05/05") - C6>=2, "Yes", "No")

Cheers

 

[TonyJollans]

Hi sprog,

When you've made your comparison work you will find the result is in DAYS, not years.

A better way is to use DATEDIF ...
[blue][tt] =IF(DATEDIF(C6,"01/05/05","y")>=2,"Yes","No")[/tt][/blue]

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[

 

[SkipVought]

Just another suggestion: Enter the date in a cell, and reference that cell instead of the date in your formula.

For instance, using Tony's formula, if you entered the date to compare in AA1:
[tt][blue]
=IF(DATEDIF(C6,$AA$1,"y")>=2,"Yes","No")
[/blue][/tt]


Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[TonyJollans]

And just to keep the suggestions rolling in (Hi Skip ) ...

You could alternatively use a defined name, say DateToCompare, set to 01/05/05 and then use

[blue][tt] =IF(DATEDIF(C6,DateToCompare,"y")>=2,"Yes","No")[/tt][/blue]

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[

 

[SkipVought]

all kinds of ways to skin a cat ;-)

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[sprog]

Thanks to everyone who replied. The first suggestion Tony has made seems to work perfectly.
Much appreciated.
Sprog