# Thread: Help With Date Formula (XL03)

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

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

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

#### Posting Permissions

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