Hello all ...

I have this formula that gives me a date 10 month in the future based on the
date in M3:
=IF(E3="","",IF(M3="",DATE(YEAR(E3),MONTH(E3)+10,DAY(E3)),DATE(YEAR(M3),MONTH(M3)+10,DAY(M3))))

.... however I want to add 2 other columns/cells so that instead of only
having one date to refer to, I would have dates in M3, N3 and O3. M3, if it
has an entry, will always have the oldest date. I want to only use the most
recent date from the range.

For instance M3 may have 23/08/2009, N3 may have 1/9/2009 and O3 may have
6/10/2010. I only want the formula to take the most recent date being in O3.

Any thoughts please. Thank you.

On Oct 29, 6:27*am, "Andrew" <theajarn...@gmail.com> wrote:
> Hello all ...
>
> I have this formula that gives me a date 10 month in the future based on the
> date in M3:
> =IF(E3="","",IF(M3="",DATE(YEAR(E3),MONTH(E3)+10,DAY(E3)),DATE(YEAR(M3),MON*TH(M3)+10,DAY(M3))))
>
> ... however I want to add 2 other columns/cells so that instead of only
> having one date to refer to, I would have dates in M3, N3 and O3. M3, if it
> has an entry, will always have the oldest date. I want to only use the most
> recent date from the range.
>
> For instance M3 may have 23/08/2009, N3 may have 1/9/2009 and O3 may have
> 6/10/2010. I only want the formula to take the most recent date being in O3.
>
> Any thoughts please. Thank you.

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

On Oct 29, 4:27*am, "Andrew" <theajarn...@gmail.com> wrote:
> I have this formula that gives me a date 10 month in the future based on the
> date in M3:
> =IF(E3="","",IF(M3="",DATE(YEAR(E3),MONTH(E3)+10,DAY(E3)),DATE(YEAR(M3),MONTH(M3)+10,DAY(M3))))
>
> ... however I want to add 2 other columns/cells so that instead of only
> having one date to refer to, I would have dates in M3, N3 and O3. M3, if it
> has an entry, will always have the oldest date. I want to only use the most
> recent date from the range.
>
> For instance M3 may have 23/08/2009, N3 may have 1/9/2009 and O3 may have
> 6/10/2010. I only want the formula to take the most recent date being in O3.

I would suggest that you put the following into P3:

=IF(COUNT(M3:O3),MAX(M3:O3),"")

and use P3 instead M3 in your formula. Alternatively:

=IF(E3="", "", IF(MAX(M3:O3)=0, DATE(YEAR(E3), MONTH(E3)+10, DAY(E3)),
DATE(YEAR(MAX(M3:O3)), MONTH(MAX(M3:O3))+10, DAY(MAX(M3:O3)))))


PS: For broader participation, you might want to post future
inquiries using the MS Answers Forums at
http://social.answers.microsoft.com/...ry/officeexcel.
It's not that I like that forum. It's just that MS has ceased to
support the Usenet newsgroups. Hence, participation here is limited
to the sites that share a common newsgroup mirror, which is no longer
centralized at MS.

On Oct 29, 4:49*pm, joeu2004 <joeu2...@hotmail.com> wrote:
> On Oct 29, 4:27*am, "Andrew" <theajarn...@gmail.com> wrote:
>
> > I have this formula that gives me a date 10 month in the future based on the
> > date in M3:
> > =IF(E3="","",IF(M3="",DATE(YEAR(E3),MONTH(E3)+10,DAY(E3)),DATE(YEAR(M3),MON*TH(M3)+10,DAY(M3))))
>
> > ... however I want to add 2 other columns/cells so that instead of only
> > having one date to refer to, I would have dates in M3, N3 and O3. M3, if it
> > has an entry, will always have the oldest date. I want to only use the most
> > recent date from the range.
>
> > For instance M3 may have 23/08/2009, N3 may have 1/9/2009 and O3 may have
> > 6/10/2010. I only want the formula to take the most recent date being in O3.
>
> I would suggest that you put the following into P3:
>
> =IF(COUNT(M3:O3),MAX(M3:O3),"")
>
> and use P3 instead M3 in your formula. *Alternatively:
>
> =IF(E3="", "", IF(MAX(M3:O3)=0, DATE(YEAR(E3), MONTH(E3)+10, DAY(E3)),
> DATE(YEAR(MAX(M3:O3)), MONTH(MAX(M3:O3))+10, DAY(MAX(M3:O3)))))
>
> PS: *For broader participation, you might want to post future
> inquiries using the MS Answers Forums athttp://social.answers.microsoft.com/Forums/en-US/category/officeexcel.
> It's not that I like that forum. *It's just that MS has ceased to
> support the Usenet newsgroups. *Hence, participation here is limited
> to the sites that share a common newsgroup mirror, which is no longer
> centralized at MS.

After seeing the file I also suggested this but using the actual
columns desired K3:M3 for column N

=IF(E3="", "", IF(MAX(M3:O3)=0, DATE(YEAR(E3), MONTH(E3)+10, DAY(E3)),
> DATE(YEAR(MAX(M3:O3)), MONTH(MAX(M3:O3))+10, DAY(MAX(M3:O3)))))

"Don Guillett Excel MVP" <(E-Mail Removed)> wrote in message
news:6194c029-7a2d-4b2c-989f-(E-Mail Removed)...
On Oct 29, 4:49 pm, joeu2004 <joeu2...@hotmail.com> wrote:
> On Oct 29, 4:27 am, "Andrew" <theajarn...@gmail.com> wrote:
>
> > I have this formula that gives me a date 10 month in the future based on
> > the
> > date in M3:
> > =IF(E3="","",IF(M3="",DATE(YEAR(E3),MONTH(E3)+10,DAY(E3)),DATE(YEAR(M3),MON*TH(M3)+10,DAY(M3))))
>
> > ... however I want to add 2 other columns/cells so that instead of only
> > having one date to refer to, I would have dates in M3, N3 and O3. M3, if
> > it
> > has an entry, will always have the oldest date. I want to only use the
> > most
> > recent date from the range.
>
> > For instance M3 may have 23/08/2009, N3 may have 1/9/2009 and O3 may
> > have
> > 6/10/2010. I only want the formula to take the most recent date being in
> > O3.
>
> I would suggest that you put the following into P3:
>
> =IF(COUNT(M3:O3),MAX(M3:O3),"")
>
> and use P3 instead M3 in your formula. Alternatively:
>
> =IF(E3="", "", IF(MAX(M3:O3)=0, DATE(YEAR(E3), MONTH(E3)+10, DAY(E3)),
> DATE(YEAR(MAX(M3:O3)), MONTH(MAX(M3:O3))+10, DAY(MAX(M3:O3)))))
>
> PS: For broader participation, you might want to post future
> inquiries using the MS Answers Forums
> athttp://social.answers.microsoft.com/Forums/en-US/category/officeexcel.
> It's not that I like that forum. It's just that MS has ceased to
> support the Usenet newsgroups. Hence, participation here is limited
> to the sites that share a common newsgroup mirror, which is no longer
> centralized at MS.

>After seeing the file I also suggested this but using the actual
>columns desired K3:M3 for column N

>=IF(E3="", "", IF(MAX(M3:O3)=0, DATE(YEAR(E3), MONTH(E3)+10, DAY(E3)),
> DATE(YEAR(MAX(M3:O3)), MONTH(MAX(M3:O3))+10, DAY(MAX(M3:O3)))))

Don and Joe ... it is done. Formula in N4 is
=IF(E4="","",IF(MAX(K4:M4)="","NEVER",(YEAR(TODAY())-YEAR(MAX(K4:M4)))*12+MONTH(TODAY())-MONTH(MAX(K4:M4))))
.... this gives me the number of months since the latest date in K4:M4

Also in O4
=IF(E4="","",IF(MAX(K4:M4)="",DATE(YEAR(E4),MONTH(E4)+10,DAY(E4)),DATE(YEAR(MAX(K4:M4)),MONTH(MAX(K4:M4))+10,DAY(MAX(K4:M4)))))
... this gives me the future date based on K4:M4.

Thank you sincerely very much for your wisdom and personal time. Cheers.

On Oct 29, 4:36*pm, "Andrew" <theajarn...@gmail.com> wrote:
> Formula in N4 is
> =IF(E4="","",IF(MAX(K4:M4)="","NEVER",(YEAR(TODAY())-YEAR(MAX(K4:M4)))*12+MONTH(TODAY())-MONTH(MAX(K4:M4))))
[....]
> Also in O4
> =IF(E4="","",IF(MAX(K4:M4)="",DATE(YEAR(E4),MONTH(E4)+10,DAY(E4)),DATE(YEAR(MAX(K4:M4)),MONTH(MAX(K4:M4))+10,DAY(MAX(K4:M4)))))

FYI, MAX(K4:M4) will never return the null string (""). It will
return zero instead.

On Oct 30, 2:33*am, joeu2004 <joeu2...@hotmail.com> wrote:
> On Oct 29, 4:36*pm, "Andrew" <theajarn...@gmail.com> wrote:
>
> > Formula in N4 is
> > =IF(E4="","",IF(MAX(K4:M4)="","NEVER",(YEAR(TODAY())-YEAR(MAX(K4:M4)))*12+M*ONTH(TODAY())-MONTH(MAX(K4:M4))))
> [....]
> > Also in O4
> > =IF(E4="","",IF(MAX(K4:M4)="",DATE(YEAR(E4),MONTH(E4)+10,DAY(E4)),DATE(YEAR*(MAX(K4:M4)),MONTH(MAX(K4:M4))+10,DAY(MAX(K4:M4)))))
>
> FYI, MAX(K4:M4) will never return the null string (""). *It will
> return zero instead.

The formula when copied down did indeed produce a ""

"joeu2004" <(E-Mail Removed)> wrote in message
news:21772ec5-6f6a-46f4-9b35-(E-Mail Removed)...
On Oct 29, 4:36 pm, "Andrew" <theajarn...@gmail.com> wrote:
> Formula in N4 is
> =IF(E4="","",IF(MAX(K4:M4)="","NEVER",(YEAR(TODAY())-YEAR(MAX(K4:M4)))*12+MONTH(TODAY())-MONTH(MAX(K4:M4))))
[....]
> Also in O4
> =IF(E4="","",IF(MAX(K4:M4)="",DATE(YEAR(E4),MONTH(E4)+10,DAY(E4)),DATE(YEAR(MAX(K4:M4)),MONTH(MAX(K4:M4))+10,DAY(MAX(K4:M4)))))

>FYI, MAX(K4:M4) will never return the null string (""). >It will
return zero instead.

Then can I ask how if K4:M4 is blank, I can have "NEVER" returned in N4
please?
Also in O4 if K4:M4 is blank, the date is shown as dd/mm/1900 instead of a
date 10 months after date shown in E4? How do I get the correct date?

Thank you.

PURCHASE WAR'TEE EXPIRY AGE OF ITEM RECOM'ED SERVICE INTERVAL MTHS
LAST SERVICE DATE 1 LAST SERVICE DATE 2 LAST SERVICE DATE 3 LAST SERVICE
DATE 4 MTHS SINCE LAST SERVICE DATE OF NEXT SERVICE NOTICE
11/07/2003 8 yrs 3 mths 12 13/08/2010 2 13/06/2011
11/07/2003 8 yrs 3 mths 12 1329 31/10/1900

This is what is happening with the MAX command as Joe noted. Ref my 9:51pm
post.

On Oct 30, 8:58*am, "Andrew" <theajarn...@gmail.com> wrote:
> * * * PURCHASE WAR'TEE EXPIRY AGE OF ITEM RECOM'ED SERVICE INTERVALMTHS
> LAST SERVICE DATE 1 LAST SERVICE DATE 2 LAST SERVICE DATE 3 LAST SERVICE
> DATE 4 MTHS SINCE LAST SERVICE DATE OF NEXT SERVICE NOTICE
> * * * 11/07/2003 8 yrs 3 mths 12 13/08/2010 * * * 2 13/06/2011
> * * * 11/07/2003 8 yrs 3 mths 12 * * * * 1329 31/10/1900
>
> This is what is happening with the MAX command as Joe noted. Ref my 9:51pm
> post.

Send me your file with the example shown