# Thread: Problem with calculation in a time duration formula

1. ## Problem with calculation in a time duration formula

Hi group,

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!

2. Why the RC[-6] in there, why not a structured reference?

Can you post the workbook, it would help.

3. ## Here is a spreadsheet detailing the formula and issue

DB V2.xlsm

Here it is attached, Bob.
Thanks

4. Is it

=IFERROR(RIGHT([@[Current Sem]],2)-RIGHT([@[Start Sem]],2)+

5. Originally Posted by Bob Phillips
Is it

=IFERROR(RIGHT([@[Current Sem]],2)-RIGHT([@[Start Sem]],2)+
Bob, This does seem the fix the problem with Spring start semesters in "Current Time in Program" column, but it records 2012-2013 as 3.5 years (which should be 2 years). The formula also does not change the Years to graduate for any of the rows. Attached is the example with comments

Thanks for the help!