Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Help With Date Formula (XL03)

    If A1:A31 contained the dates Oct 01, 2006 through Oct 31, 2006, what formulas could be placed in B1:B5 that would list the Sunday dates from the A list? The list of dates in the A column will change monthly so that Sunday obviously is not always in the sames cells. The formulas in B would need to find the five possible Sundays in the month and list just those dates in B1:B5.

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    - Ricky

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

    Re: Help With Date Formula (XL03)

    Put this formula in the cell where you want the date of the first Sunday of the month whose date is in cell A1:

    <code>
    =IF(MONTH($A$1-WEEKDAY($A$1,1)+1)=MONTH($A$1),$A$1-WEEKDAY($A$1,1)+1,$A$1-WEEKDAY($A$1,1)+8)
    </code>


    Put the formulas below in the cells where you want the dates of the second through the fifth Sunday dates, changing all occurances of $E$1 to the cell address where you put the formula above:

    <code>
    =IF(MONTH(E1)=MONTH(E1+7),E1+7,"")
    =IF(MONTH($E$1)=MONTH($E$1+7),$E$1+7,"")
    =IF(MONTH($E$1)=MONTH($E$1+14),$E$1+14,"")
    =IF(MONTH($E$1)=MONTH($E$1+21),$E$1+21,"")
    =IF(MONTH($E$1)=MONTH($E$1+28),$E$1+28,"")
    </code>
    Legare Coleman

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help With Date Formula (XL03)

    Assuming that A1 always contains the first day of a month, you can use these formulas in B1 to B5:
    <code>
    =IF(WEEKDAY(A1)=1,A1,A1+8-WEEKDAY(A1))
    =B1+7
    =B2+7
    =B3+7
    =IF(B4+7<DATE(YEAR(A1),MONTH(A1)+1,1),B4+7,"")
    </code>
    The latter formula returns a blank if there is no 5th Sunday in the month.

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Thank You

    A sincere thanks to Legare and Hans. So glad you were online on this Sunday evening to assist.

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    - Ricky

Posting Permissions

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