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
Hi,
Yes I have checked that. For any date, the formula is calculating 60. Please check again and thank you for your input. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF for Aging in excel - Help needed
Now it is calculating 60 for any date.
Please check again. |
#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. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF for Aging in excel - Help needed
I would appreciate if someone can have a look at this again.
Thx. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF for Aging in excel - Help needed
"Angela" wrote:
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 MediaFi http://www.mediafire.com FileFactory: http://www.filefactory.com FileSavr: http://www.filesavr.com FileDropper: http://www.filedropper.com RapidSha 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. |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF for Aging in excel - Help needed
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 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF for Aging in excel - Help needed
Guys I am having trouble using this in access :(
|
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) |