accepting most recent date across columns

  • Thread starter Thread starter Andrew
  • Start date Start date
A

Andrew

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.
 
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.
Click to expand...

"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."
 
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.
Click to expand...

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/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.
 
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.
Click to expand...

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)),
 
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.
Click to expand...
After seeing the file I also suggested this but using the actual
columns desired K3:M3 for column N
Click to expand...
=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)))))
Click to expand...

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.
 
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)))))
Click to expand...

FYI, MAX(K4:M4) will never return the null string (""). It will
return zero instead.
 
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)))))
Click to expand...

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

The formula when copied down did indeed produce a ""
 
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)))))
Click to expand...
FYI, MAX(K4:M4) will never return the null string (""). >It will
Click to expand...
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.
 
      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.
Click to expand...

Send me your file with the example shown
 
Back
Top