Solved: Retirement Spreadsheet Help

[ads_3131]

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?

Attached is my spreadsheet....

[IanFScott]

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.

[Bob Phillips]

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]

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

[ads_3131]

Thanks guys, Ive had to break my formula up otherwise i get a #Value! error...

My formula which solved all this is now :
=IF(AND($IV33>D10,$IV33<=E10),"INFORM",IF(AND($C33>D10,$C33<=E10),"RETIREME NT",""))

Thanks for putting me on the right lines