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

1. 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. 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>

3. 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. Re: Making a nested IF statement simpler. (Excel 2000/SR 1)

Here's an excellent and amusing explanation of =DATEDIF() from Chip Pearson.

5. 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

Posting Permissions

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