# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  IF to calculate if one date is greater than another

## Tin

Hi,

I am having great difficulties getting the following formula to calulate wether the date in cell f2 is greather than of less than the given date.

=IF(F2<="18.11.06", "No Cover", "Under Warranty")

f2 = 14.09.02

This does produce the wanted result of "No cover" however if the date is 14.09.07 and therefore greater the 18.11.06 i will not get the expected result of "Under Warranty"

Any ideas?? Help would be greatly appreciated.

----------


## Carim

Hi,

When dealing with dates, you are better off using
=date(year,month,day)
and format the way you want ...

HTH
Carim
 :Smilie:

----------


## VBA Noob

Hi,

Try

=IF(F2<=DATEVALUE("18/11/06"), "No Cover", "Under Warranty")

VBA Noob

----------


## Tin

Thanks for your help guys,

VBA Noob your formula works a treat.. thanks..!!

 :Smilie:

----------


## Carim

Tin,

If I may ... VBA Noob's suggestion with datevalue() is an efficient patch, but you should take the habit, when working with dates, to use the date() function...

I know VBA Noob would agree ...

HTH
Carim
 :Smilie:

----------


## VBA Noob

No Problem

Glad it helped you but I agree with Carim

VBA Noob

----------


## Tin

Nice one guys,

I will try that one. 

I now have another problem that you may be able to help with.

I am now trying to calculate if a cells value matches with a range of values in a column on another worksheet, but again I am not getting the expected results. The formula is as follows:

=IF(H2=Sheet1!K2:K152,"No Cover","Under Warranty")

the value of H2 is a server name (UKABEFPS012) and the values on sheet1 between k2 and k152 are also server names.

I trying to calculate if ukabefps012 appears in the list on sheet1 and if it does to display "No Cover" and if it's not in the list to display "Under Warranty"

Any Ideas whats wrong with the formula??

----------


## VBA Noob

This might work

=IF(VLOOKUP(H2,Sheet1!K2:K152,1,FALSE)=H2,"No Cover","Under Warranty")

Think you might have to sway the true and false statements. If in list then Uner Warranty ??

VBA Noob

----------


## Tin

Hmm a bit of a wierd one...

if I use that formula all my results come back with #N/A if I replace the false with true then I get all "Under Warranty"

Appologies for all the questions i'm new to excel. :Confused:

----------


## VBA Noob

Hi,

Sorry should of being

=IF(ISERR(VLOOKUP(H2,Sheet1!K2:K152,1,FALSE)),"No Cover","Under Warranty")

VBA Noob

----------


## Carim

Could following help ... ?




```
Please Login or Register  to view this content.
```


HTH
Carim
 :Smilie:

----------


## Tin

thanks guys,

VBA if I try your formula I just get the formula displayed and Carim if I try yours I get #NAME?

Any ideas?

----------


## Tin

Sorry VBA Noob must have been my error i've tried your formula again and i get all "Under Warranty"

----------


## starguy

> Sorry VBA Noob must have been my error i've tried your formula again and i get all "Under Warranty"



try this

=IF(ISERROR(VLOOKUP(H2,Sheet1!K2:K152,1,FALSE)),"No Cover","Under Warranty")

----------


## oldchippy

> Hi,
> 
> Sorry should of being
> 
> =IF(ISERR(VLOOKUP(H2,Sheet1!K2:K152,1,FALSE)),"No Cover","Under Warranty")
> 
> VBA Noob



Just a small typo here causing a problem

=IF(*ISERROR*(VLOOKUP(H2,Sheet1!K2:K152,1,FALSE)),"No Cover","Under Warranty")

----------


## Tin

Stary Guy thanks for that,

It almost works, it is differentiating between what appears on the list an what is not, but it appears in the opposite order... So where a match is found it produces "under Warranty" and when there is no match it produces "No Cover" when it should be the other way around.

Can you give me a brief explaination as to what the stages of your formula is doing please? Just to help me understand better!

----------


## oldchippy

Switch the TRUE & FALSE in the statement

=IF(ISERROR(VLOOKUP(H2,Sheet1!K2:K152,1,FALSE)),"Under Warranty","No Cover")

----------


## Tin

Sorry to be a pain but how can I also add another comman to that line so that if H2 was blank it would produce a given response for example.

=IF(H2="","Please Check"(otherwise carry out the calculation of the formula)

??

----------


## starguy

try this

=IF(H2="","",IF(ISERROR(VLOOKUP(H2,Sheet1!K2:K152,1,FALSE)),"Under Warranty","No Cover"))

----------


## oldchippy

=IF(H2="","Please Check",IF(ISERROR(VLOOKUP(H2,Sheet1!K2:K152,1,FALSE)),"Under Warranty","No Cover"))

----------


## starguy

for understanding VLOOKUP formula
visit this

http://www.contextures.com/xlFunctions02.html

----------


## Tin

Thanks guys, your being a great help!!!

if i try both formula's and with changing the false to a true i get all "Under Warranty"

----------


## starguy

> Thanks guys, your being a great help!!!
> 
> if i try both formula's and with changing the false to a true i get all "Under Warranty"



copy this formula, double click on the cell and paste it. hopefully it would work.

=IF(H2="","",IF(ISERROR(VLOOKUP(H2,Sheet1!$K$2:$K$152, 1,FALSE)),"Under Warranty","No Cover"))

or use this one if you want to show "Please Check" when cell H2 is blank

=IF(H2="","Please Check",IF(ISERROR(VLOOKUP(H2,Sheet1!$K$2:$K$152, 1,FALSE)),"Under Warranty","No Cover"))

----------


## Tin

Thanks a lot star guy...

All you guys have been a great help...!!

Will try tomorrow and get back to you...

Have a good night..!

 :Smilie:

----------

