PDA

View Full Version : Solved: Retirement Spreadsheet Help



ads_3131
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?

Attached is my spreadsheet....

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

ads_3131
03-13-2012, 02:04 AM
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),"RETIREMENT",""))

Thanks for putting me on the right lines :D