1. ## 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. ## Re: Latest date

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

WEEKNUM

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. ## 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. ## 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. ## Re: Latest date

Nice gif there eggs...

6. ## 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.

7. ## 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
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>

8. ## 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. ## 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
•