dates of month

[rejoyce40]

I have a date [i.e. 12/1/2008] that is entered in a cell, then I want all the
Sunday dates of that month to automatically enter in non-adjacent cells on
the form.

I've searched and cannot get this to work - help would be appreciated!!

thanks!

 

[Bernie Deitrick]

With 12/1 entered into cell A1, use

=DATE(YEAR(A1),MONTH(A1),1)+CHOOSE(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),0,6,5,4,3,2,1)

for the first Sunday, then for the next Sundays, use

=DATE(YEAR(A1),MONTH(A1),1)+CHOOSE(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),0,6,5,4,3,2,1) + 7
=DATE(YEAR(A1),MONTH(A1),1)+CHOOSE(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),0,6,5,4,3,2,1) + 14
=DATE(YEAR(A1),MONTH(A1),1)+CHOOSE(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),0,6,5,4,3,2,1) + 21
and if there is a fifth Sunday:
==IF(MONTH(DATE(YEAR(A1),MONTH(A1),1)+CHOOSE(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),0,6,5,4,3,2,1) + 28
)=MONTH(A1),DATE(YEAR(A1),MONTH(A1),1)+CHOOSE(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),0,6,5,4,3,2,1) +
28,"")

HTH,
Bernie
MS Excel MVP

 

[T. Valko]

Try these:

A1 = some date

A3 = 1st Sunday
A5 = 2nd Sunday
A7 = 3rd Sunday
A9 = 4th Sunday
A11 = 5th Sunday (if there is one)

Enter these formulas in their respective cells:

A3: for the 1st Sunday

=A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+1-1)

A5: for the 2nd Sunday

=A3+7

A7: for the 3rd Sunday

=A3+14

A9: for the 4th Sunday

=A3+21

A11: for the 5th Sunday (if ther is one)

=IF(MONTH(A9+7)=MONTH(A1),A9+7,"")

You might have to format the cells as Date

 

[Bernd P]

Non-adjacent?? How?

Regards,
Bernd

 

[rejoyce40]

Wow, that worked just exactly like I wanted - THANK YOU, THANK YOU, THANK
YOU!!!

Try these:

A1 = some date

A3 = 1st Sunday
A5 = 2nd Sunday
A7 = 3rd Sunday
A9 = 4th Sunday
A11 = 5th Sunday (if there is one)

Enter these formulas in their respective cells:

A3: for the 1st Sunday

=A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+1-1)

A5: for the 2nd Sunday

=A3+7

A7: for the 3rd Sunday

=A3+14

A9: for the 4th Sunday

=A3+21

A11: for the 5th Sunday (if ther is one)

=IF(MONTH(A9+7)=MONTH(A1),A9+7,"")

You might have to format the cells as Date


--
Biff
Microsoft Excel MVP

I have a date [i.e. 12/1/2008] that is entered in a cell, then I want all
the
Sunday dates of that month to automatically enter in non-adjacent cells on
the form.

I've searched and cannot get this to work - help would be appreciated!!

thanks!

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Wow, that worked just exactly like I wanted - THANK YOU, THANK YOU, THANK
YOU!!!

Try these:

A1 = some date

A3 = 1st Sunday
A5 = 2nd Sunday
A7 = 3rd Sunday
A9 = 4th Sunday
A11 = 5th Sunday (if there is one)

Enter these formulas in their respective cells:

A3: for the 1st Sunday

=A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+1-1)

A5: for the 2nd Sunday

=A3+7

A7: for the 3rd Sunday

=A3+14

A9: for the 4th Sunday

=A3+21

A11: for the 5th Sunday (if ther is one)

=IF(MONTH(A9+7)=MONTH(A1),A9+7,"")

You might have to format the cells as Date


--
Biff
Microsoft Excel MVP

I have a date [i.e. 12/1/2008] that is entered in a cell, then I want
all
the
Sunday dates of that month to automatically enter in non-adjacent cells
on
the form.

I've searched and cannot get this to work - help would be appreciated!!

thanks!