SoonerTexan
New Member
- Joined
- Feb 8, 2013
- Messages
- 11
Hello,
I'm needing to calculate a retirement date based on a set of rules and data I have in a spreadsheet. I have a formula now that does some of what I need and could be modified, but it might not be the best way to do this to begin with.
Here are the rules (whatever rule is effective first is the rule that applies):
1. 55 years old AND 10 years of service (for example, a 54 year old with 15 years of service would hit this rule on his/her 55th birthday and a 57 year old with 9 years of service would hit this rule on his/her 10th service anniversary)
2. OR 65 years old and 0 years of service
3. OR If the person has hit either of the above conditions and is retirement eligible before an arbitrary date I will call "Start Date," then the person's new retirement eligible date is 6 months + Start date (For example, if the "Start Date" were 1/1/2013, the new date would be 7/1/2013)
My Data is set up like below (the first row is the column number--there is other non-relevant info I need to keep together in this sheet)
[TABLE="width: 500"]
<tbody>[TR]
[TD]B[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Start Date[/TD]
[TD]Date of Birth[/TD]
[TD]Hire Date[/TD]
[TD]Age 55 Date[/TD]
[TD]Age 65 Date[/TD]
[TD]10 Year Service Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I created Columns L, M, and N myself with modifications of this formula to make it easier for me to write an overall simpler retirement date calculation formula.
So after all that background, here is my question:
I'm using this formula to calculate a retirement date for rules 1 & 2 above only. It does not factor in rule #3 and I need it to:
<n2,l2,n2))[ code]
</n2,l2,n2))[><n2,l2,n2))[ code]
How can I modify this so it will take into account Rule #3 as well? Also, is there a better way to do this that eliminates having to use Columns L, M, and N altogether and simply uses Columns B, E, and F? I'm a little hesitant to go that route because I think it will be one whopper of a formula that may be hard to follow, and I really need this to be as clear as possible and easy enough for someone else to follow due to what I'm using this as a check for.
Any ideas?</n2,l2,n2))
</n2,l2,n2))[>
I'm needing to calculate a retirement date based on a set of rules and data I have in a spreadsheet. I have a formula now that does some of what I need and could be modified, but it might not be the best way to do this to begin with.
Here are the rules (whatever rule is effective first is the rule that applies):
1. 55 years old AND 10 years of service (for example, a 54 year old with 15 years of service would hit this rule on his/her 55th birthday and a 57 year old with 9 years of service would hit this rule on his/her 10th service anniversary)
2. OR 65 years old and 0 years of service
3. OR If the person has hit either of the above conditions and is retirement eligible before an arbitrary date I will call "Start Date," then the person's new retirement eligible date is 6 months + Start date (For example, if the "Start Date" were 1/1/2013, the new date would be 7/1/2013)
My Data is set up like below (the first row is the column number--there is other non-relevant info I need to keep together in this sheet)
[TABLE="width: 500"]
<tbody>[TR]
[TD]B[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Start Date[/TD]
[TD]Date of Birth[/TD]
[TD]Hire Date[/TD]
[TD]Age 55 Date[/TD]
[TD]Age 65 Date[/TD]
[TD]10 Year Service Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I created Columns L, M, and N myself with modifications of this formula to make it easier for me to write an overall simpler retirement date calculation formula.
=DATE(YEAR(E2)+65,MONTH(E2),DAY(E2))
So after all that background, here is my question:
I'm using this formula to calculate a retirement date for rules 1 & 2 above only. It does not factor in rule #3 and I need it to:
<n2,l2,n2))[ code]
</n2,l2,n2))[><n2,l2,n2))[ code]
<n2,l2,n2))=IF(AND(L2>=N2,N2<=M2),M2,IF(L2<N2,L2,N2))</n2,l2,n2))[><n2,l2,n2))[ code]
How can I modify this so it will take into account Rule #3 as well? Also, is there a better way to do this that eliminates having to use Columns L, M, and N altogether and simply uses Columns B, E, and F? I'm a little hesitant to go that route because I think it will be one whopper of a formula that may be hard to follow, and I really need this to be as clear as possible and easy enough for someone else to follow due to what I'm using this as a check for.
Any ideas?</n2,l2,n2))
</n2,l2,n2))[>
Last edited: