Invoice aging - custom function

[transferxxx]

Am currently trying to write a custom function for aging of my
invoices which will be used in a macro in my Debtor statement report
- but it's not working as it looks to simple to be good. Can anybody
help thxs.

Function Age(StatementDate, InvDate)
If StatementDate - InvDate <= 30 Then Age = "Current"
If StatementDate - InvDate >= 30 Then Age = "30 days"
If StatementDate - InvDate >= 60 Then Age = "60 days"
If StatementDate - InvDate >= 90 Then Age = "90 days"
If StatementDate >= 150 Then Age = "150 days"
End Function

StatementDate & Invdate are in date format.

 

[Bernie Deitrick]

Change
If StatementDate >= 150 Then Age = "150 days"

to
If StatementDate - InvDate >= 150 Then Age = "150 days"

StatementDate will always be > 150, unless the date is in the first 5 months
of 1900....

HTH,
Bernie
MS Excel MVP

 

[Dave Peterson]

Maybe something like:

Option Explicit
Function Age(StatementDate As Date, InvDate As Date) As String

Dim Diff As Long
Dim myStr As String

Diff = StatementDate - InvDate

Select Case Diff
Case Is <= 30: myStr = "Current"
Case Is < 60: myStr = "30 days"
Case Is < 90: myStr = "60 days"
Case Is < 150: myStr = "90 days"
Case Else
myStr = "150 days"
End Select

Age = myStr

End Function

 

[Guest]

Hi,

I would say you have a problem in the ordering of your conditional statements.

Say, for example, StatementDate - InvDate = 65. The first condition that
matches is:
If StatementDate - InvDate >= 30. As this condition is met then the
following conditional statements will not be tested (Presuming they are all
within the same If block using ElseIf). Try this instead:

If StatementDate - InvDate >= 150 Then Age = "150 days"
ElseIf StatementDate - InvDate >= 90 Then Age = "90 days"
ElseIf StatementDate - InvDate >= 60 Then Age = "60 days"
ElseIf StatementDate - InvDate >= 30 Then Age = "30 days"
Esle Age = "Current"
End If

Also, your first two conditions are true for 30 as they both contain the =
sign.

You could also use Select for situations like this, it usually leaves less
writing:

Select Case StatementDate - InvDate
Case >= 150:Age = "150 days"
Case >= 90:Age = "90 days"
Case >= 60:Age = "60 days"
Case >= 30:Age = "30 days"
Case Else:Age = "Current"
End Select

I hope this helps.

Sean.