Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    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. #3
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Last sunday (2003)

    About the same length as Tony's second reply:
    <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. #5
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •