Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jun 2001
    Location
    Illinois
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Making a nested IF statement simpler. (Excel 2000/SR 1)

    Hey all, I have an IF formula:
    =IF(C10<=ReportDate+1,"LATE",IF(C10<=ReportDate+8, "Due in 1 week.",IF(C10<=ReportDate+15,"Due in 2 weeks.",IF(C10<=ReportDate+29,"Due in 1 Month",""))))
    It looks at the ReportDate and the Finish date of each task, and tells if when it's due. This works fine, but now they want me to make it do it for all the tasks, which extend to the end of 2005. That would be a lot of nested IFs, and I don't think I could handle typing it all up. Is there an easier way to do this? I looked at the "Woody's OFFICE for Mere Mortals #3.11 - Three ways to do things in Excel - 2" series of letters, but couldn't figure anything out. I created a table and all that, but don't know what formulas to use.
    Anyone know an easy way to do this?

    Thanks,
    jek

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making a nested IF statement simpler. (Excel 2000/SR 1)

    You could do something like this:

    <pre>=IF(A1<INT(NOW()+1),"LATE","Due in "&INT(DATEDIF(NOW()+1,A1,"d")/7)&" weeks")
    </pre>

    Legare Coleman

  3. #3
    Star Lounger
    Join Date
    Jun 2001
    Location
    Illinois
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Making a nested IF statement simpler. (Excel 2000/SR 1)

    Thanks, that worked after a bit of tweaking. I ended up with:
    =IF(A3="","",IF(C3<INT(ReportDate+1),"LATE","Due in "&INT((C3+4-ReportDate)/7)&" weeks"))

    I didn't understand DATEDIF().

    I may have to just to the VBA forum to ask this, but how do i insert this formula into a range?
    Now I have:
    Set CurrentCell = Range("I3")
    Set StatusRange = Range(CurrentCell, CurrentCell.End(xlDown))
    StatusRange.Formula = "=IF(A3="","",IF(C3<INT(ReportDate+1),"LATE"," Due in "&INT((C3+4-ReportDate)/7)&" weeks"))"

    But I get the error:
    Compile Error:
    Expected: end of statement
    (With LATE Highlighted)

    Thanks,
    Justin K.

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Making a nested IF statement simpler. (Excel 2000/SR 1)

    Here's an excellent and amusing explanation of =DATEDIF() from Chip Pearson.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Making a nested IF statement simpler. (Excel 2000/SR 1)

    VBA thinks that each quote is the end of a string making up the formula, so the single quotes need to be double quotes, and double quotes need to be quadruple quotes, so VBA recognizes them as quotes. See if this does what you want:

    Sub test()
    Dim statusrange As Range
    Set statusrange = Range([I3], [I3].End(xlDown))
    statusrange.FormulaR1C1 = _
    "=IF(R[0]C[-8]="""","""",IF(R[0]C[-6]<INT(ReportDate+1),""LATE"", _
    ""Due in ""&INT((R[0]C[-6]+4-ReportDate)/7)&"" weeks""))"
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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