PDA

View Full Version : Solved: Retirement Spreadsheet Help

03-08-2012, 02:14 AM
Hello!!!!

Attached i have a retirement spreadsheet that i have created, it works apart from one feature that i cannot get my head around.....

Currently i put a users d.o.b in and it calcs the date of retirement and places the word "Retirement" in the cell allocated to the date......

What i need help with is, If the cell says "retirement" put the text "Inform" 6 months before?

Can this be done?

IanFScott
03-08-2012, 06:52 AM
Yes, of course - a nested IF (Note 183 days is, near enough, 6 months):
The formula for cell E12 is:
=IF(AND((\$D12-183)>E10,(\$D12-183)<=F10),"INFORM",IF(AND(\$D12>E10,\$D\$12<=F10),"RETIREMENT",""))
Then copy across and down.

xld
03-08-2012, 08:07 AM
An alternative,

=IF(DATE(YEAR(\$D12),MONTH(\$D12)-6,1)=E\$10,"Inform",IF(DATE(YEAR(\$D12),MONTH(\$D12),1)=E\$10,"RETIREMENT",""))

Paul_Hossler
03-08-2012, 08:22 AM
What i need help with is, If the cell says "retirement" put the text "Inform" 6 months before?

Am I correct in assuming that what you want is

If (Now - DOB) > 65 years then
Display "Retirement"
Elseif (Now - DOB) > 64.5 years then
Display "Inform"
Else
Display Nothing
Endif

Paul