Function Aging

%3CLINGO-SUB%20id%3D%22lingo-sub-1990781%22%20slang%3D%22en-US%22%3EFunction%20Aging%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1990781%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20build%20a%20macro%20for%20aging%20based%20on%20agedate%20(last%20day%20of%20the%20month)%20and%20dates%20of%20service.%20What%20am%20i%20doing%20wrong%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFunction%20Aging(%20dos%20AS%20Date%2C%20agedate%20AS%20Date)%20AS%20string%3C%2FP%3E%3CP%3E%E2%80%98Calculates%20aging%20date%20based%20on%20date%20of%20service%20and%20an%20input%20age%20date'%3C%2FP%3E%3CP%3ESELECT%20CASE%20agedate%20%E2%80%93%20dos%3C%2FP%3E%3CP%3ECASE%20IS%20%26lt%3B%20%3D1%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BAging%20%3D%20%E2%80%9CDOS%20is%20after%20age%20Date!%E2%80%9D%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20CASE%20IS%20%26lt%3B%2030%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Aging%20%3D%20%E2%80%9C0-30%E2%80%9D%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20CASE%20IS%20%26lt%3B%2060%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Aging%20%3D%20%E2%80%9C31-60%E2%80%9D%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20CASE%20IS%20%26lt%3B%2090%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Aging%20%3D%20%E2%80%9C61-90%E2%80%9D%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20CASE%20IS%20%26lt%3B%20120%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Aging%20%3D%20%E2%80%9C91-120%E2%80%9D%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20CASE%20IS%20%26lt%3B%20150%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Aging%20%3D%20%E2%80%9C121-150%E2%80%9D%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20CASE%20IS%20%26lt%3B%20180%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Aging%20%3D%20%E2%80%9C151-180%E2%80%9D%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20CASE%20IS%20%26lt%3B%20210%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Aging%20%3D%20%E2%80%9C181-210%E2%80%9D%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20CASE%20IS%20%26lt%3B%20240%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Aging%20%3D%20%E2%80%9C211-240%E2%80%9D%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20CASE%20IS%20%26lt%3B%20270%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Aging%20%3D%20%E2%80%9C241-270%E2%80%9D%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20CASE%20IS%20%26lt%3B%20300%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Aging%20%3D%20%E2%80%9C271-300%E2%80%9D%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20CASE%20IS%20%26gt%3B%3D%20365%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Aging%20%3D%20%E2%80%9C%26gt%3B365%E2%80%9D%3C%2FP%3E%3CP%3EEnd%20SELECT%3C%2FP%3E%3CP%3EEND%20Function%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1990781%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1991237%22%20slang%3D%22en-US%22%3ERe%3A%20Function%20Aging%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1991237%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F902984%22%20target%3D%22_blank%22%3E%40TZullo01%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EVBA%20requires%20straight%20quotes%20instead%20of%20curly%20quotes.%3C%2FP%3E%0A%3CP%3EAlso%2C%20you're%20missing%20a%20few%20groups.%3C%2FP%3E%0A%3CP%3ETry%20this%20version%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EFunction%20Aging(dos%20As%20Date%2C%20agedate%20As%20Date)%20As%20String%0A%20%20%20%20'Calculates%20aging%20date%20based%20on%20date%20of%20service%20and%20an%20input%20age%20date%0A%20%20%20%20Select%20Case%20agedate%20-%20dos%0A%20%20%20%20%20%20%20%20Case%20Is%20%26lt%3B%200%0A%20%20%20%20%20%20%20%20%20%20%20%20Aging%20%3D%20%22DOS%20is%20after%20age%20Date!%22%0A%20%20%20%20%20%20%20%20Case%20Is%20%26lt%3B%3D%2030%0A%20%20%20%20%20%20%20%20%20%20%20%20Aging%20%3D%20%220-30%22%0A%20%20%20%20%20%20%20%20Case%20Is%20%26lt%3B%3D%2060%0A%20%20%20%20%20%20%20%20%20%20%20%20Aging%20%3D%20%2231-60%22%0A%20%20%20%20%20%20%20%20Case%20Is%20%26lt%3B%3D%2090%0A%20%20%20%20%20%20%20%20%20%20%20%20Aging%20%3D%20%2261-90%22%0A%20%20%20%20%20%20%20%20Case%20Is%20%26lt%3B%3D%20120%0A%20%20%20%20%20%20%20%20%20%20%20%20Aging%20%3D%20%2291-120%22%0A%20%20%20%20%20%20%20%20Case%20Is%20%26lt%3B%3D%20150%0A%20%20%20%20%20%20%20%20%20%20%20%20Aging%20%3D%20%22121-150%22%0A%20%20%20%20%20%20%20%20Case%20Is%20%26lt%3B%3D%20180%0A%20%20%20%20%20%20%20%20%20%20%20%20Aging%20%3D%20%22151-180%22%0A%20%20%20%20%20%20%20%20Case%20Is%20%26lt%3B%3D%20210%0A%20%20%20%20%20%20%20%20%20%20%20%20Aging%20%3D%20%22181-210%22%0A%20%20%20%20%20%20%20%20Case%20Is%20%26lt%3B%3D%20240%0A%20%20%20%20%20%20%20%20%20%20%20%20Aging%20%3D%20%22211-240%22%0A%20%20%20%20%20%20%20%20Case%20Is%20%26lt%3B%3D%20270%0A%20%20%20%20%20%20%20%20%20%20%20%20Aging%20%3D%20%22241-270%22%0A%20%20%20%20%20%20%20%20Case%20Is%20%26lt%3B%3D%20300%0A%20%20%20%20%20%20%20%20%20%20%20%20Aging%20%3D%20%22271-300%22%0A%20%20%20%20%20%20%20%20Case%20Is%20%26lt%3B%3D%20330%0A%20%20%20%20%20%20%20%20%20%20%20%20Aging%20%3D%20%22301-330%22%0A%20%20%20%20%20%20%20%20Case%20Is%20%26lt%3B%3D%20365%0A%20%20%20%20%20%20%20%20%20%20%20%20Aging%20%3D%20%22331-365%22%0A%20%20%20%20%20%20%20%20Case%20Else%0A%20%20%20%20%20%20%20%20%20%20%20%20Aging%20%3D%20%22%26gt%3B365%22%0A%20%20%20%20End%20Select%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20AgeDate%20is%20in%20C1%20and%20the%20dates%20in%20B3%20and%20down.%3C%2FP%3E%0A%3CP%3EEnter%20the%20following%20formula%20in%20C3%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DAging(B3%2C%24C%241)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eand%20fill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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

1 Reply

@TZullo01 

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.