Dec 15 2020 12:07 PM
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
Dec 15 2020 02:39 PM
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.