Results 1 to 5 of 5

Thread: Problem with calculation in a time duration formula

  1. #1

    Problem with calculation in a time duration formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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. #2
    Why the RC[-6] in there, why not a structured reference?

    Can you post the workbook, it would help.

  3. #3

    Here is a spreadsheet detailing the formula and issue

    DB V2.xlsm

    Here it is attached, Bob.
    Thanks

  4. #4
    Is it

    =IFERROR(RIGHT([@[Current Sem]],2)-RIGHT([@[Start Sem]],2)+
    (IF(LEFT([@[Start Sem]],2)="FA",0,IF(LEFT([@[Start Sem]],2)="SP",-1,0.5))),[@[Years To Graduate]])

  5. #5
    Quote Originally Posted by Bob Phillips View Post
    Is it

    =IFERROR(RIGHT([@[Current Sem]],2)-RIGHT([@[Start Sem]],2)+
    (IF(LEFT([@[Start Sem]],2)="FA",0,IF(LEFT([@[Start Sem]],2)="SP",-1,0.5))),[@[Years To Graduate]])
    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!
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •