AR Aging - will not skip blank cells

[cabotiger]

I have an aging report that will calaulate the ages of an invoice and than put in a corresponding column. The only problem is that it is taking all blanks and returning "#VALUE!" in cells with text and is returning "40257" on blank cells. I only want it to age if there is a real DATE in column "A".

[lucas]

Try this in D16 you can drag it down.

=IF(ISNONTEXT(A16),(TODAY()-A16),"")

[lucas]

Whoops, I missed the second if statement for the numbers. Try this in A16 and drag it down.

=IF(A16<>"",IF(ISNONTEXT(A16),(TODAY()-A16),""),"")

[cabotiger]

I tried that but still got an error on the blank cells. I changed it to =IF(ISNUMBER(A16),(TODAY()-A16),"") and that worked. Right now i have the formula value in a macro. Do you know how i could fix that?

[mdmackillop]

=IF(AND(ISNUMBER(A16),A16>0),TODAY()-A16,"")

[cabotiger]

This is the macro that i was using before- Is it possible to change this?


Sub daysBetweenDates()
Dim myDest As Range
Range("D16:D26").FormulaR1C1 = "=(TODAY()-RC[-3])"
Select Case Range("C16").Value
Case 0 To 30: Set myDest = Range("E16:E26")
Case 31 To 60: Set myDest = Range("F16:F26")
Case 61 To 90: Set myDest = Range("G16:G26")
Case 91 To 120: Set myDest = Range("H16:H26")
Case Is > 120: Set myDest = Range("I16:I26")
End Select
Range("C16:C26").Copy myDest
End Sub

[mdmackillop]

You could also use a User Defined Function (UDF) and enter =AGE(A16)


Function Age(Dte As Range)
If IsDate(Dte) Then
Age = Date - Dte
Else: Age = ""
End If
End Function