# Solved: Help in date formula

Discussion in 'Business Applications' started by ayush, Dec 29, 2011.

Not open for further replies.

Joined:
Mar 27, 2005
Messages:
86
A person retires at the age of 58 years. If his date of birth is on any day between 2nd and month end.
Ex: if DOB is 15-10-2000 date of retirement will be 31-10-2058

This formula works OK for above
=DATE(YEAR(A1)+58,MONTH(A1)+1,DAY(0))

But if the date of birth is on 1st day of the month his retirement will be last day of previous month.
Ex:
01 Oct 2010 his retirement date will be 30 Nov 2058
01 Jan 1996 his retirement date will be 31 Dec 2033

I want both of them together using if or any other mode of formula

Pl. help me with suitable formula

2. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,203
First Name:
Wayne
this may work - not tested fully
I used in row 2

=IF(DATE(YEAR(A2),MONTH(A2),DAY(A2))=DATE(YEAR(A2),MONTH(A2),1),DATE(YEAR(A2)+58,MONTH(A2),DAY(0)),DATE(YEAR(A2)+58,MONTH(A2)+1,DAY(0)))

Joined:
Mar 27, 2005
Messages:
86
Dear Sir,

It worked. Thank You and a Happy New Year to You and Tech Support Guy Team

4. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,203
First Name:
Wayne
excellent - thanks for the card

5. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
Less is more.

=DATE(YEAR(A1)+58,IF(DAY(A1)=1,MONTH(A1),MONTH(A1)+1),DAY(0))

Joined:
Mar 27, 2005
Messages:
86
Thank You verymuch sir.

7. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,203
First Name:
Wayne
much neater

Happy New Year

8. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
TBH I just wanted my own card.

9. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,203
First Name:
Wayne
and very nice they are too

As Seen On