Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF for Aging in excel - Help needed
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF for Aging in excel - Help needed
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF for Aging in excel - Help needed
It gives a #value! error.
Not working. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF for Aging in excel - Help needed
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF for Aging in excel - Help needed
Now it is calculating 60 for any date.
Please check again. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF for Aging in excel - Help needed
Hi,
Yes I have checked that. For any date, the formula is calculating 60. Please check again and thank you for your input. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF for Aging in excel - Help needed
"Angela" wrote:
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). Angela wrote: 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. Angela wrote: 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF for Aging in excel - Help needed
"Angela" wrote:
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). Angela wrote: 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. Angela wrote: 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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF for Aging in excel - Help needed
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF for Aging in excel - Help needed
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 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.) |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF for Aging in excel - Help needed
"Angela" wrote:
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 Angela wrote: 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. Angela wrote: 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF for Aging in excel - Help needed
Hi Joeu,
Many thanks for your reply. :) ya that was as apostrophe :P 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 ------------------------------------------------------- Many thanks for your patience and help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
UDF for Aging in excel - Help needed | Excel Programming | |||
aging category calculation for open purchase order report in Excel | Excel Worksheet Functions | |||
Excel for inventory aging? | Excel Worksheet Functions | |||
How can I pull and format an SAP aging report into Excel? | Excel Programming | |||
How to make aging possible in Excel for an age trial balance shee. | Excel Discussion (Misc queries) |