Problem with calculation in a time duration formula

Hi group,

I joined Excel forum because I hope someone can help me with this tricky formula. I'm trying to calculate the duration of time (years) where start and stop years can be formatted "2012" "FA12" or "SP12". You may have guessed that this is to calculate when a person started and ended an education program. From left to right, there is a column for START, a column for END, and a column for "CTIP" (Current time in program). The formula in the CTIP cell is

=IFERROR([@[Years To Graduate]],(IF(LEFT([@[Start Sem]],2)=LEFT([@[Current Sem]],2),((RIGHT([@[Current Sem]],2)-(RIGHT(RC[-6],2)))),(RIGHT([@[Current Sem]],2)-(RIGHT(RC[-6],2)+0.5)))))

If the start date is FA12, we get the correct answer in CTIP. If the start date is SP12, the CTIP reports an entire year too soon (e.g., it reads 1 for SP12 to FA13 when it should be 2).

Any help is appreciated!

I think this is what you are looking for...

I'm not entirely sure I undertand which cells exactly are being referenced where, so use the below formula and just select <End Date> in the formula bar in Excel and then click on which cell represents the end date, and likewise for <Start Date>.

=IFERROR([@[Years To Graduate]], RIGHT(<End Date>,2)-RIGHT(<Start Date>,2) +0.5 - IF(LEFT(<End Date>,2)="FS",1,0) + IF(LEFT(<Start Date>,2)="FS",1,0))