UDF for Aging in excel - Help needed

[Angela]

Hi,

I am working on an aging formula in excel.
I have date in any cell and if I want to test age, I just use below
excel formula to see the age bracket.

I have managed it in as an array formula in excel but now I need it to
be a UDF.
=LOOKUP(TODAY()-Y2,
{0,7.51,14.51,21.51,30.51,45.51,60.51;"0-7","8-14","15-21","22-30","31-45","46-60",">60"})

Below is what I've been trying to do.

Code:
Function AgeBucket(strValue) As String

Dim strValue As Date
Dim Rng As Range
Dim Rng2 As Range

Rng = Array(0, 7.51, 14.51, 21.51, 30.51, 45.51, 60.51)
Rng2 = Array("0-7", "8-14", "15-21", "22-30", "31-45", "46-60", ">60")

AgeBucket = Application.WorksheetFunction.Lookup(Today - strValue,
Rng; Rng2)

End Function

Any input is more than welcome.
Thanks

 

[Javed]

Pls try.....

Function AgeBucket(strValue) As String

AgeBucket = Application.Evaluate("=LOOKUP(today()-" & strValue & ",
{0,7.51,14.51,21.51,30.51,45.51,60.51;""0-7"",""8-14"",""15-21"",""22-30"",""31-45"",""46-60"","">60""})")
'Comment---This line will be in one single line otherwise error



End Function

 

[Angela]

It gives a #value! error.
Not working.

 

[Javed]

I checked and then posted.

After pasting in standard module
you will find one hiphen (-) sign before ""31-45""
","8-14","15-21","22-30",-"31-45",­"46-60

remove that and it will work.You can check my code and after pasting
module.There was no - sign.

 

[Angela]

Hi,

Yes I have checked that.
For any date, the formula is calculating >60.

Please check again and thank you for your input.

 

[Angela]

Now it is calculating >60 for any date.
Please check again.

 

[joeu2004]

I have date in any cell and if I want to test age,
I just use below excel formula to see the age bracket.
I have managed it in as an array formula in excel [...].
=LOOKUP(TODAY()-Y2,
{0,7.51,14.51,21.51,30.51,45.51,60.51;
"0-7","8-14","15-21","22-30","31-45","46-60",">60"})

First, your Excel formula should be:

=LOOKUP(TODAY()-Y2,{0,8,15,22,31,46,61;
"0-7","8-14","15-21","22-30","31-45","46-60",">60"})

This assumes that Y2 contains only an Excel date, not date and time. If the
latter, use TODAY()-INT(Y2).

now I need it to be a UDF.

Why? If you call the UDF from an Excel formula, it will be much slower than
LOOKUP.

Function AgeBucket(strValue) As String
Dim strValue As Date
Dim Rng As Range
Dim Rng2 As Range
Rng = Array(0, 7.51, 14.51, 21.51, 30.51, 45.51, 60.51)
Rng2 = Array("0-7", "8-14", "15-21", "22-30", "31-45", "46-60", ">60")
AgeBucket = Application.WorksheetFunction.Lookup(Today - strValue,
Rng; Rng2)
End Function

Numerous syntax errors. Also, the variable name "strValue" is misleading.
I presume you call it using AgeBucket(Y2), and I assume that Y2 contains an
Excel date, not the string "7/9/2011" for example. In that case, write:


Function AgeBucket(origDate As Date) As String
Dim ageLimit As Variant
Dim ageRng As Variant
ageLimit = Array(0, 8, 15, 22, 31, 46, 61)
ageRng = Array("0-7", "8-14", "15-21", "22-30", _
"31-45", "46-60", ">60")
AgeBucket= WorksheetFunction.Lookup(Date - origDate, _
ageLimit, ageRng)
End Function


If origDate might contain time as well as a date, use Date - Int(origDate).


Note:
1. Declare type of parameters (origDate) in the Function statement.
2. Assign Array(...) to a Variant variable.
3. Use VBA Date function instead of Today(), which is an Excel function.

 

[joeu2004]

I have date in any cell and if I want to test age,
I just use below excel formula to see the age bracket.
I have managed it in as an array formula in excel [...].
=LOOKUP(TODAY()-Y2,
{0,7.51,14.51,21.51,30.51,45.51,60.51;
"0-7","8-14","15-21","22-30","31-45","46-60",">60"})

First, your Excel formula should be:

=LOOKUP(TODAY()-Y2,{0,8,15,22,31,46,61;
"0-7","8-14","15-21","22-30","31-45","46-60",">60"})

This assumes that Y2 contains only an Excel date, not date and time. If the
latter, use TODAY()-INT(Y2).

now I need it to be a UDF.

Why? If you call the UDF from an Excel formula, it will be much slower than
LOOKUP.

Function AgeBucket(strValue) As String
Dim strValue As Date
Dim Rng As Range
Dim Rng2 As Range
Rng = Array(0, 7.51, 14.51, 21.51, 30.51, 45.51, 60.51)
Rng2 = Array("0-7", "8-14", "15-21", "22-30", "31-45", "46-60", ">60")
AgeBucket = Application.WorksheetFunction.Lookup(Today - strValue,
Rng; Rng2)
End Function

Numerous syntax errors. Also, the variable name "strValue" is misleading.
I presume you call it using AgeBucket(Y2), and I assume that Y2 contains an
Excel date, not the string "7/9/2011" for example. In that case, write:


Function AgeBucket(origDate As Date) As String
Dim ageLimit As Variant
Dim ageRng As Variant
ageLimit = Array(0, 8, 15, 22, 31, 46, 61)
ageRng = Array("0-7", "8-14", "15-21", "22-30", _
"31-45", "46-60", ">60")
AgeBucket= WorksheetFunction.Lookup(Date - origDate, _
ageLimit, ageRng)
End Function


If origDate might contain time as well as a date, use Date - Int(origDate).


Note:
1. Declare type of parameters (origDate) in the Function statement.
2. Assign Array(...) to a Variant variable.
3. Use VBA Date function instead of Today(), which is an Excel function.

 

[Angela]

Thanks Joeu2004 & Javed..

This works now
Joeu, if you see my first post, I was trying to do the same as you
mentioned in your last 3 points but I do not know how to script that.
My logic I guess was clear.. just need to understand how to right code
and imporve on that.

Thanks both once again.

 

[Angela]

Hi,

Just need to add a validation to above function w.r.t. below senarios.

I'm having trouble dealing with different types/formats of dates as
well as need not to calculate incase there is no date.


S# Description Senario Result
1 Space Date is greater than current date.(If there is just space
in a cell)
2 Blank #VALUE!
3 Alpha AAAaaa Date is greater than current date.
4 Numeric 123 #VALUE!
5 Special *^(*(*#*&# Date is greater than current date.
6 Mix **jas7G##^2 Date is greater than current date.
7 Email (e-mail address removed) Date is greater than current date.
8 Date 8-Jul 0-7
9 Date (General Format) 40732 #VALUE! (This is date in general
format---->Ctrl+1 and selected general.)
10 Date (General Format)40732 Date is greater than current date.
(With this I have added a ' comma in the start to make it look like a
text date---> '40732)
11 Greater Date 7-Aug Date is greater than current date.
12 Old date 13-Feb >60

I can change the error messages that the function would return incase
it does not find a date.
Just need to figure out to recognize the value in the cell to be any
date format (short date, general etc.)

 

[joeu2004]

Just need to add a validation to above function w.r.t.
below senarios.
I'm having trouble dealing with different types/formats
of dates as well as need not to calculate incase there
is no date.

Assuming you want to return the null string for all invalid conditions:


Function AgeBucket(origDate) As String
Const maxAge As Long = 365
Dim ageLimit As Variant
Dim ageRng As Variant
Dim x As Long
AgeBucket = ""
If IsEmpty(origDate) Then Exit Function
If Not WorksheetFunction.IsNumber(origDate) _
Then Exit Function
x = Date - origDate
If x < 0 Or x > maxAge Then Exit Function
ageLimit = Array(0, 8, 15, 22, 31, 46, 61)
ageRng = Array("0-7", "8-14", "15-21", "22-30", _
"31-45", "46-60", ">60")
AgeBucket = WorksheetFunction.Lookup(x, ageLimit, ageRng)
End Function

4 Numeric 123 #VALUE!

You cannot distinguish between 123 and a valid date. Excel dates are simply
integers, namely the number of days since 12/31/1899, which Excel display as
1/0/1900. The number 123 is the date 5/2/1900.

In order to cover this case, I added the constant maxAge. It is currently
to 365 (one year). Make that larger or smaller as you wish to weed out
out-of-range numbers that are probably not dates.

I have added a ' comma in the start to make it look
like a text date---> '40732)

The character is an apostrophe (aka single-quote), not a comma. It is
unclear whether you want to allow that as long as it represents a valid date
(i.e. less than TODAY(), but not by more than maxAge days), or if you want
to disallow it as text, which it is.

The implemenation above disallows it as text. If you want to allow it,
change WorksheetFunction.IsNumber to IsNumeric, a VBA function.

-----

Alternatively, you could use Excel Data Validation to disallow any data
entry other than a date. You can even specify the range of acceptable
dates.

In that case, the function can be simplified as follows:


Function AgeBucket(origDate) As String
Dim ageLimit As Variant
Dim ageRng As Variant
If IsEmpty(origDate) Then AgeBucket = "": Exit Function
ageLimit = Array(0, 8, 15, 22, 31, 46, 61)
ageRng = Array("0-7", "8-14", "15-21", "22-30", _
"31-45", "46-60", ">60")
AgeBucket = WorksheetFunction.Lookup(Date - origDate, _
ageLimit, ageRng)
End Function

 

[Angela]

Hi Joeu,

Many thanks for your reply.

ya that was as apostrophe

I have checked both your codes with variations but I'm unable to
satisfy a list of senarios with any one of them at the moment.

We are very close.
I have tested 25 senarios and would like to share with you.
I'm so far successful with 24. Only stuck with 1.
Please let me know how to share my findings in an excel sheet with
you.
I would like to share a table with all 25 senarios that I'm looking
into.

Here is the code that I am working on.. This works with 24 cases and
is only left with 1.
------------------------------------
Function AgeBucket(TargetDate) As String

Dim AgeLmt As Variant
Dim AgeRng As Variant

TargetDateG = Format(TargetDate, "Short Date")

AgeLmt = Array(0, 7.51, 14.51, 21.51, 30.51, 45.51, 60.51)
AgeRng = Array("0-7", "8-14", "15-21", "22-30", "31-45", "46-60",
">60")

If TargetDate = TargetDateG Or TargetDate < 1 Then
AgeBucket = "Invalid date or out of range"
Else

If CDate(TargetDate) > Date Then
AgeBucket = "Date is greater than current date."
Else

If CDate(TargetDate) <= Date Then
AgeBucket = WorksheetFunction.Lookup(Date - CDate(TargetDate), AgeLmt,
AgeRng)
End If
End If
End If
End Function

 

[Angela]

I would appreciate if someone can have a look at this again.
Thx.

 

[joeu2004]

I would appreciate if someone can have a look at this again.

Sorry, I did not read your previous posting closely, and I did not see your
request for follow-up.

Angela wrote previously:

I have tested 25 senarios and would like to share
with you. I'm so far successful with 24. Only stuck
with 1.

Your implementation has a number of deficiencies. See below.

Did you try the implemenation I offered?

Angela wrote previously:

Please let me know how to share my findings in an excel
sheet with you. I would like to share a table with all
25 senarios that I'm looking into.

You could send the Excel file to me directly. Send it joeu2004 "at"
hotmail.com.

Alternativly, upload the Excel file to a file-sharing website and post the
URL (link; http://...) in a response here. Be sure that the uploaded file
is marked shared or sharable on the file-sharing website. The following is
a list of some free file-sharing websites. I use box.net/files.

Windows Live Skydrive: http://skydrive.live.com
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
FileDropper: http://www.filedropper.com
RapidShare: http://www.rapidshare.com
Box.Net: http://www.box.net/files

Some comments on the code you posted....

Angela wrote previously:

TargetDateG = Format(TargetDate, "Short Date")

There is no need to format TargetDate in some date form. The date format
does not make any difference.

If you are trying to remove a non-zero time component, it is sufficient to
do:

TargetDate = Int(TargetDate)

Angela wrote previously:

AgeLmt = Array(0, 7.51, 14.51, 21.51, 30.51, 45.51, 60.51)

As I explained before, there is no need for the decimal fractions 0.51,
especially if you use Int(TargetDate) as I suggested previously and above.

Angela wrote previously:

If TargetDate = TargetDateG Or TargetDate < 1 Then
AgeBucket = "Invalid date or out of range"

The test "< 1" catches only "dates" that are time-only. I suspect that you
also would like to treat numbers like 1234 as "out of range". After all,
that is the date 5/18/1903.

Previously, I suggested that you hardcode a reasonable constant least-likely
age. I chose 365 (one year) arbitrarily.

Alternatively, you could have a constant least-likely date. For example:

Const earliestDate = #1/1/2010#

Moreover, your implementation will result in a "type mismatch" VBA error if
TargetDate is a non-numeric string.

I wonder if that is 25th scenario that your implementation fails with.

Previously, I provided a reasonable implementation that would avoid this.
Did you try my implemenation?

Angela wrote previously:

If CDate(TargetDate) > Date Then

The only reason to use CDate is if TargetDate might be a __string__ whose
content is interpreted to be a date, not an Excel date, which is numeric.

Is that the case?

Well, to answer that, I really do need to see the Excel file. The English
description is fraught with potential misunderstanding and
misinterpretation.

 

[Angela]

Hi,

Yes I have tried with variations but I need a single function to cover
all of them.
I have sent you the email with a table and the add-in file.

Many thanks for your response.
Much appreciated.


Angela

 

[Angela]

Guys I am having trouble using this in access