Results 1 to 9 of 9

Thread: Latest date

  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Morecambe England, Lancashire, England
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Latest date

    I wonder if anyone has the answer to this one I'm using Windows 98 and Excel 2000

    People give money to our church as and when they want to. So person 1 might give something in say week 1, 7, 9 and 11. Person 2 may give in week 4, 8, 9 and 13 person 3 could give in weeks 1,2, 6, 8, 12, 17 and 19

    Now it

  2. #2
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Latest date

    You want the "Weeknum" function. This is what the help file says

    WEEKNUM
    See Also

    Returns a number that indicates where the week falls numerically within a year.

    If this function is not available, run the Setup program to install the Analysis ToolPak. After you install the Analysis ToolPak, you must enable it by using the Add-Ins command on the Tools menu.

    How?

    Syntax

    WEEKNUM(serial_num,return_type)

    Serial_num is a date within the week. For more information about how Microsoft Excel uses serial numbers for dates, see the Remarks section.

    Return_type is a number that determines on what day the week begins. The default is 1.

    Serial num Week Begins
    1 Week begins on Sunday. Weekdays are numbered 1 through 7.
    2 Week begins on Monday. Weekdays are numbered 1 through 7.


    Remarks

    Microsoft Excel stores dates as sequential serial numbers so that it can perform calculations on them. Excel stores January 1, 1900, as serial number 1 if your workbook uses the 1900 date system. If your workbook uses the 1904 date system, Excel stores January 1, 1904, as serial number 0 (January 2, 1904, is serial number 1). For example, in the 1900 date system, Excel stores January 1, 1998, as serial number 35796 because it is 35,795 days after January 1, 1900. Learn more about how Microsoft Excel stores dates and times.

    Examples

    If date is Sunday, January 10, 1999, then:

    WEEKNUM("1/10/1999",1) equals 3

    WEEKNUM("1/10/1999",2) equals 2

    WEEKNUM("2005/08/31") equals 36

    So using this in a 3rd column will allow you to sort, or whatever, with the appropriate week number.

    Hope that helps.

    E 'n' B

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Latest date

    Sorry that I don't have an answer for you offhand, but it did get me looking at some of the conditional formulas Excel has. SumIf. CountIf.

    But no MaxIf or MinIf. <img src=/S/frown.gif border=0 alt=frown width=15 height=15>

    I can see quite a bit of potential for these two "functions". Anyone have any thoughts on how they might be able to be programmed to be used as functions?

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Latest date

    Hi Michael,

    Check out the MAXA worksheet function in Excel 2000. In a test sheet, this returned the latest date in a column of cells formatted as date.

    In my opinion it would be more meaningful to supply the last date of payment as opposed to the week, however I am no expert on tax laws where you are located. If you should however, wish to calculate the week, you could use the WEEKNUM function on the result of the MAXA function to return the week number of the value returned by MAXA as eggs 'n' bacon proposed.

    For ex:

    =WEEKNUM(MAXA(A1:A6))

    returns 23 upon the range A1 to A6 where the largest value is 6/09/01.

    HTH,

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Latest date

    Nice gif there eggs...

  6. #6
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Latest date

    Are you looking for an array formula something like this:

    {=MAX(IF($A$1:$A$100=D1,$B$1:$B$100,0))}

    where A1:100 and B1:B100 contain the names and dates of the contributions, D1 to D{whatever} contains a list of the names of all contributors and the formula is copied down from E1 to E{whatever}, so that the D1 changes to match the appropriate row.

    That may not be the clearest explanation, so I've attached a small example. (The dates are printed in d/mm/yyyy format, which is standard in Australia)

    Ian.
    Attached Files Attached Files

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Latest date

    What does your spreadsheet look like? I am guessing that it looks something like this:

    <pre> 1/7/01 1/14/01 1/21/01 1/28/01 2/4/01 2/11/01 Last Total
    Doe, Jane $20.00 $20.00 $20.00 2/4/01 $60.00
    Smith, John $50.00 $50.00 1/14/01 $100.00
    </pre>


    With names down column A and Sunday dates across row 1. If so, then you could use a User Defined Function Like this:

    <pre>Public Function GetLast(dGifts As Range) As Integer
    Dim I As Integer, iLast As Integer, oCell As Range
    I = 0
    For Each oCell In dGifts
    If oCell.Value <> "" Then
    iLast = I
    End If
    I = I + 1
    Next oCell
    GetLast = iLast
    End Function
    </pre>


    Then you would put a formula like this in the column where you want the last date:

    <pre>=OFFSET($B$1,0,getlast(B2:G2))
    </pre>

    Legare Coleman

  8. #8
    dpcs9kz
    Guest

    Re: Latest date

    If your names and money are set up in cells as follows:
    1 2 3 4 5 <weekNo
    Name1 $4 $5
    Name2 $10
    etc
    Then the following array formula put in cell to right of last week column and copied down should do the trick:
    =MAX((B$1:M$1)*((B2:M2)<>""))
    where B1:M1 are cells containing week numbers, ie 1,2,3,etc.
    and B2:M2 are cells containing weekly money (or are blank).
    Remember to set up that 1st formula as an array formula
    (cursor in edit box, Ctrl+Shift+Enter)
    HTH
    Ken Lalonde

  9. #9
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Morecambe England, Lancashire, England
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Latest date

    Can I just thank all of you who were kind enough to help me on this problem.

    Long live Woody's Lounge!!

    Best wishes

    Michael Peak

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •