Trying to calculate age in number of days

**V-Clan** · 10-14-2014, 01:28 PM

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.

**Bob Phillips** · 10-14-2014, 01:50 PM

Why not just use a formula

=EOMONTH(A2,0)-A2+1

**V-Clan** · 10-14-2014, 03:19 PM

Bob, thanks for your reply I appreciate it. Currently, that's what I'm doing (using a formula). I was looking at a Macro because there are multiple procedures that need to occur to get the spreadsheet workable (adding in data in various fields, etc) and with the thousands of rows I'm working with I figured it would be simpler to throw it in a macro.

The formula I'm using is =sum($M$1-N2) where M is the EOM date and N is my column of various dates. This gives me the number of days old a particular line is. Example, EOM sep 30 2014, line is august 15, 2014. Formula returns 380 as a number. Having this in a macro would be more ideal as after this calculation occurs I need to convert that number into an age 'bucket' depending on how old it is. For example, anything between 366-730 would simply be 730, anything 61-90 days old would be 90.