 # Function Aging

Hello,

I am trying to build a macro for aging based on agedate (last day of the month) and dates of service. What am i doing wrong?

Function Aging( dos AS Date, agedate AS Date) AS string

‘Calculates aging date based on date of service and an input age date'

SELECT CASE agedate – dos

CASE IS < =1

Aging = “DOS is after age Date!”

CASE IS < 30

Aging = “0-30”

CASE IS < 60

Aging = “31-60”

CASE IS < 90

Aging = “61-90”

CASE IS < 120

Aging = “91-120”

CASE IS < 150

Aging = “121-150”

CASE IS < 180

Aging = “151-180”

CASE IS < 210

Aging = “181-210”

CASE IS < 240

Aging = “211-240”

CASE IS < 270

Aging = “241-270”

CASE IS < 300

Aging = “271-300”

CASE IS >= 365

Aging = “>365”

End SELECT

END Function

# Re: Function Aging

VBA requires straight quotes instead of curly quotes.

Also, you're missing a few groups.

Try this version:

``````Function Aging(dos As Date, agedate As Date) As String
'Calculates aging date based on date of service and an input age date
Select Case agedate - dos
Case Is < 0
Aging = "DOS is after age Date!"
Case Is <= 30
Aging = "0-30"
Case Is <= 60
Aging = "31-60"
Case Is <= 90
Aging = "61-90"
Case Is <= 120
Aging = "91-120"
Case Is <= 150
Aging = "121-150"
Case Is <= 180
Aging = "151-180"
Case Is <= 210
Aging = "181-210"
Case Is <= 240
Aging = "211-240"
Case Is <= 270
Aging = "241-270"
Case Is <= 300
Aging = "271-300"
Case Is <= 330
Aging = "301-330"
Case Is <= 365
Aging = "331-365"
Case Else
Aging = ">365"
End Select
End Function``````

Let's say AgeDate is in C1 and the dates in B3 and down.

Enter the following formula in C3;

=Aging(B3,\$C\$1)

and fill down.