Formula for state retirement age

The base case formula is

=IF(B1="F",DATE(YEAR(A1)+60,MONTH(A1),DAY(A1)),DATE(YEAR(A1)+65,MONTH(A1),DAY(A1)))

Where column A contains the date of birth and column B contains F/M to indicate gender. However, you then have the problem of the gradual increase in female retirement age from 2010 to 2020 (and this is on a daily basis, not jumping months or years in steps) and the increase in both from 2024 to 2046. I can't immediately ses a solution that doesn't involve overly complex nested if statements.

Would using the online calclator for each new client be simpler?  

Sorry to hijack the post, but that link is really useful. Thank you.

Assume A2 contains M or F for gender

Assume B2 contains date of birth

To give retirement age try:

=IF(AND("06/04/1959"-B2>0,A2="M"),65,IF(AND("06/04/1950"-B2>0,A2="F"),60,IF(AND(A2="F","05/04/1950"-B2<0,"06/04/1955"-B2>0),MIN(65,60+YEAR(B2)-1950+((MOD(MONTH(B2)-4,12)+MIN(1,MAX(DAY(B2)-5,0)))/12)),65)))

This should give the age including a decimal for number of months (e.g. 63.0833 = 63 + 1 month).  I am not convinced it works perfectly for DOB's around the 6th of the month though.

Assuming the above formula is in C2 to give retirement date try:

=DATE(YEAR(B2)+INT(C2),MONTH(B2)+(C2-INT(C2))*12,IF(OR(B2-"06/04/1950"<0,B2-"05/04/1955">0),DAY(B2),6))

This will, at present only work for dates of birth until 5/4/1959.  Dates of birth after this fall into the period whereby the age increases past 65 (as noted by previous poster).  I have stopped because I do not have the time to 'finish' the formula, but if you can follow the logic of the first formula it should be straightforward (famous last words) to continue it by replacing the final 65 with further similar IF statements to cater for 2024/2026, 2034/2036 and 2044/2046.  I will try and have a go later if you let me know you are interested - but not promising anything.

Now, two other suggestions, try or ask someone to write a macro rather than have it encapsulated in one long formula, which will be nigh on impossible to maintain (I created the one above and it looks daunting to me after 30 minutes) or listen to the first poster, its a nightmare to do easily try the online checker.

If you want to try the above formulas and cannot paste them into Excel, send me a mail and I will e-mail you the sheet on which I was working with it.

My first thought was some form of look up table, but I was trying to be a smart arse (and failing) by answering your question to the letter, i.e. one formula.

Good luck with it whichever way you go