1. ## Last sunday (2003)

If I have a date in a cell, which formula would tell me the last Sunday of its month? That is, which date the last Sunday falls on.
Thanks, Andy.

2. ## Re: Last sunday (2003)

Try this:

<code>=DATE(A1,A2+1,1)+IF(1<WEEKDAY(DATE(A1,A2+1,1 )),7-WEEKDAY(DATE(A1,A2+1,1))+1,1-WEEKDAY(DATE(A1,A2+1,1)))-7</code>

A1 contains the year
A2 contains the month as a number 1-12

I'm sure Hans can come up with something simpler <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

3. ## Re: Last sunday (2003)

Version 2, a bit simpler

<code>=DATE(YEAR(A1),MONTH(A1)+1,0)-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0))+1</code>

A1 contains the date

4. ## Re: Last sunday (2003)

<code>
=DATE(YEAR(A1),MONTH(A1)+1,0)-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)
</code>
This version uses the 3rd argument of WEEKDAY instead of adding 1 to the result.

5. ## Re: Last sunday (2003)

Thanks very much everyone. I'd forgotten about the second argument for WEEKDAY.
I notice that Excel is quite clever about dates. For example, if you add 1 to 12 in terms of months it knows that it reverts to January of the following year. Andy.

6. ## Re: Last sunday (2003)

> I notice that Excel is quite clever about dates.

Another example: in Tony's second formula and in mine, 0 is supplied as day, this results in the last day of the previous month (0 January = 31 December)

#### Posting Permissions

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