Converting Years to Days

[Robby19]

This sounds simple. There are 365 days in a year so YearValue*365, right? Unfortunately no. There is not 365 days in every year, this in itself makes me sad. But I digress.

So I am trying to figure out what a persons "mandatory retirement date" would be based off of certain criteria. The certain criteria part is easy, what I am having trouble with is excel calculating the right date.

For example; If I entered in 2009/08/17 and have reached a position where my mandatory retirement date is 20 years later, on the last day of the month, then my retirement date should be 2029/08/31.

There are other instances where 20 is not always the answer either, there are instances where it could be 8, 13, 20, 22, 27, and 30. HOWEVER, if the separation date is less than 20 years, i.e. 8 or 13, then the retirement date is on the same day they are to be released.

For example; 2009/08/17 and did not reach a certain position, now my mandatory separation date is 13 years, which would be 2022/08/17.

Can anyone help me figure this out?

 

[razaas]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Entered date[/TD]
[TD]Separation years[/TD]
[TD]Retirement date[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]17-Aug-09<strike></strike>[/TD]
[TD]<strike></strike>20[/TD]
[TD]Enter given formula here and drag down[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]17-Aug-09<strike></strike>[/TD]
[TD]<strike></strike>13[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

=IF(B2>=20,DATE(YEAR(A2)+B2,MONTH(A2),DAY(EOMONTH(A2,0))),DATE(YEAR(A2)+B2,MONTH(A2),DAY(A2)))

 

[joeu2004]

As a matter of law, I believe your premise is incorrect.

The annual anniversary date of an event is the same month and day of a future year.

The only exception is Feb 29. AFAIK, Feb 28 is the correct annual anniversary date of Feb 29, if there is no Feb 29 that year.

So if D1 contains the original date and N1 contains the number of years:

=EDATE(D1, 12*N1)

is the annual anniversary date.

 

[Peter_SSs]

@razaas
Your formula may have some problems with leap years - try it with the examples in rows 4 & 5 of my sample data below.
@Robby19
See if this formula, copied down, does what you want. Is that what you expect the results to be? If not what results do you want and why?

Excel Workbook
	A	B	C
1	Date	Years	Retirement
2	17/08/2009	20	31/08/2029
3	17/08/2009	13	17/08/2022
4	29/02/2008	22	28/02/2030
5	15/02/1994	22	29/02/2016
6	05/12/2010	9	05/12/2019
Final Date

 

[Robby19]

@Robby19
See if this formula, copied down, does what you want. Is that what you expect the results to be? If not what results do you want and why.

Thank you for the formula. This seems to work as well as the other one. What about adding in additional criteria, for example;

If Rank Value = 3 and DCC = AW then it should be separation date + a number to bring them to April of the following year. The day does not matter, it just needs to get them to April.

If Rank Value = 3 and DCC = AT then it should show their current separation date.

If Rank Value = 5 and DCC = AW or AT and current total service is less than 20 years, it should show their 20 year mark. If over 20 years, it should be their current separation date.

If Rank value = 6 and DCC = AW or AT and current total service is less than 22 years, it should show their 22 year mark. If over 22 years, it should be their current separation date.

I know this is a lot, but I am truly stumped.

 

[Robby19]

So far I have the Rank Val = 3 formula written

=IF(AND([@[Rank Val]]=3,[@DCC]="AW",[@[April Calc]]<0),[@EAS],IF(AND([@[Rank Val]]=3,[@DCC]="AW",[@[April Calc]]>0),[@ECC]+[@[April Calc]],IF(AND([@[Rank Val]]=3,[@DCC]="AT"),[@EAS],IF(AND([@[Rank Val]]=3,[@DCC]="AT"),[@EAS],IF([@[Service Limit]]<20,EDATE([@AFADBD],[@[Service Limit]]*12),EOMONTH([@AFADBD],[@[Service Limit]]*12))))))

This seems to work fine. Any ideas how I can get the others. If I am not mistaken, I believe the nested IF limit is 7 IF statements, correct? The way I am doing this, it will end up being more than 7.

 

[Robby19]

I'm sure there is a more efficient way to write this, but my brain is jello. So whatever.

=IF(AND([@[Rank Val]]=3,[@DCC]="AW",[@[April Calc]]<0),[@EAS],IF(AND([@[Rank Val]]=3,[@DCC]="AW",[@[April Calc]]>0),[@ECC]+[@[April Calc]],IF(AND([@[Rank Val]]=3,[@DCC]="AT"),[@EAS],IF(AND([@[Rank Val]]=5,[@DCC]="AW",[@EAS]>[@[20 Yr Mark]]),[@EAS],IF(AND([@[Rank Val]]=5,[@DCC]="AT"),[@EAS],IF(AND([@[Rank Val]]=6,[@DCC]="AW",[@EAS]>[@[20 Yr Mark]]),[@EAS],IF(AND([@[Rank Val]]=6,[@DCC]="AT"),[@EAS],IF([@[Service Limit]]<=20,EDATE([@AFADBD],[@[Service Limit]]*12),EOMONTH([@AFADBD],[@[Service Limit]]*12)))))))))

 

[Peter_SSs]

I have no idea what "Rank Value" or "DCC" is or where they are located or what they have to do with the problem.