Hello all -

I'm trying to calculate the age of something by days old. Column N has various dates and i'm trying to bump those dates against a standard End of Month reporting date. For example, in September the date was 9/30/2014. For this month it would be 10/31/2014. Here's the code I have so far but I'm not able to get it to work correctly. All it's doing is playing my EOM date in the adjacent column but i can't figure out what I'm doing wrong. Here's my code:

Sub AgeCat()
Dim rptdt1 As Date
Dim rng As Range
Dim i As Long

rptdt1 = DateValue("30/Sep/2014")
Set rng = Range("N2:N" & Range("N" & Rows.Count).End(xlUp).Row)
For Each Cell In rng
If Cell.Value <> "" Then
Cell.Offset(0, -1).Formula = (rptdt1 - N2)
End If
Next
End Sub

Thank you for your help. I'm hoping what I'm trying to do is possible, but i'm having trouble creating a function within the macro that uses my Dim rptdt1 with cell data in column N.