conversion to EDATE

ss
Guest
Posts: n/a

 17th Aug 2012
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")

Stan Brown
Guest
Posts: n/a

 17th Aug 2012
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
Shikata ga nai...

joeu2004
Guest
Posts: n/a

 17th Aug 2012
"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")

ss
Guest
Posts: n/a

 17th Aug 2012
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.

joeu2004
Guest
Posts: n/a

 17th Aug 2012
"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"))

ss
Guest
Posts: n/a

 18th Aug 2012
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

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Karin Stiles Microsoft Excel Discussion 4 29th Oct 2004 01:46 AM =?Utf-8?B?S2V2aW4gTWNDYXJ0bmV5?= Microsoft Excel Programming 1 11th Mar 2004 12:15 PM Israel Rodriguez Microsoft Excel Discussion 3 29th Jan 2004 08:31 AM Israel Rodriguez Microsoft Excel Programming 3 29th Jan 2004 08:31 AM fdiez67 Microsoft Excel Programming 3 18th Nov 2003 09:13 PM

Features

Newsgroups

All times are GMT +1. The time now is 02:57 PM.