Having got edate working now can the formula below be converted, I have
tried a few options but obviously making errors.
The >180 represents 6 months, I am looking to deduct 6 months in
preference to 180 days.

=IF(D5-D11>180,"","RENEW")

On Fri, 17 Aug 2012 09:59:38 +0100, ss wrote:
>
> Having got edate working now can the formula below be converted, I have
> tried a few options but obviously making errors.
> The >180 represents 6 months, I am looking to deduct 6 months in
> preference to 180 days.
>
> =IF(D5-D11>180,"","RENEW")

=IF(D5 > DATE(YEAR(D11),MONTH(D11)+6,DAY(D11)), "", "RENEW")

A nice feature of DATE() is that you can add or subtract days or
months within the function and it makes the right conversion.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
Shikata ga nai...

"ss" <(E-Mail Removed)> wrote:
> Having got edate working now can the formula below be
> converted, I have tried a few options but obviously
> making errors. The >180 represents 6 months, I am
> looking to deduct 6 months in preference to 180 days.
> =IF(D5-D11>180,"","RENEW")

=IF(D5>EDATE(D11,6),"","RENEW")

On 17/08/2012 09:59, ss wrote:
> Having got edate working now can the formula below be converted, I have
> tried a few options but obviously making errors.
> The >180 represents 6 months, I am looking to deduct 6 months in
> preference to 180 days.
>
> =IF(D5-D11>180,"","RENEW")

Thanks I have extended this to:-
=IF(D5>EDATE(D13,6),"OK","RENEW")
And this works good, however I now want a blank cell if nothing is
entered in the cell, it currently displays either OK or renew.
I tried =IF(D5>EDATE(D13,6),"OK","RENEW","",)
But this doesnt give me a blank cell.
To clarify, I need 3 options:-
either `OK`
Or `Renew`
Or a blank cell
Its the blank cell I cant fathom.

"ss" <(E-Mail Removed)> wrote:
> I have extended this to:-
> =IF(D5>EDATE(D13,6),"OK","RENEW")
> And this works good, however I now want a blank cell if
> nothing is entered in the cell, it currently displays
> either OK or renew.

What is "the" cell: D5 or D13 or both?

This should cover all bases, albeit possibly more complicated than you
require.

=IF(OR(D5="",D13=""),"",IF(D5>EDATE(D13,6),"OK","RENEW"))

On 17/08/2012 23:28, joeu2004 wrote:
> "ss" <(E-Mail Removed)> wrote:
>> I have extended this to:-
>> =IF(D5>EDATE(D13,6),"OK","RENEW")
>> And this works good, however I now want a blank cell if
>> nothing is entered in the cell, it currently displays
>> either OK or renew.
>
> What is "the" cell: D5 or D13 or both?
>
> This should cover all bases, albeit possibly more complicated than you
> require.
>
> =IF(OR(D5="",D13=""),"",IF(D5>EDATE(D13,6),"OK","RENEW"))
>
That works perfectly, thank you.

D5 is expiry date for passport
D13 Is date leaving the visiting country
And the cell the formula is in states either OK, Renew or remains blank
if no information in cell D13