Problem with Dates

[Ralph M]

I have a WorkSheet with a schedule on it and I am working with dates and formulas as the dates will change every year (the user type the year in cell "C1"), I have a special event that occurs on Sunday after Christmas but before January 1st. If there is no Sunday before Jan 1st, then December 30th. Can someone help me with the formula?
Thanks

 

[kkknie]

Not sure if this is what you want, but here goes...

=IF(WEEKDAY(Q14)>1,Q14+(8-WEEKDAY(Q14)),Q14+5)

Basically, if Christmas falls on any other day than Sunday, there will be another Sunday in December.
So if the day of the week is > 1 (Sunday) add 8 minus the day of the week to Christmas day.
Otherwise, just add 5 days to get to December 30th.

K

 

[DRJ]

Check the weekday function to test what day 12/25/2004 is and go from there.

 

[Brian from Maui]

I have a WorkSheet with a schedule on it and I am working with dates and formulas as the dates will change every year (the user type the year in cell "C1"), I have a special event that occurs on Sunday after Christmas but before January 1st. If there is no Sunday before Jan 1st, then December 30th. Can someone help me with the formula?
Thanks

That would be December 26, 2004. What are you trying to do, besides flag the Sunday after Christmas? What formulas are you using now?

 

[Ralph M]

kkknie,
That's it, thanks so much.

 

[Zack Barresse]

how about something like this?....

Book14
	A	B	C	D
1	Year	SundayBefore
2	2003	12/28/2003
Sheet2

HTH

edit: wow! i was a little slow on that one (reminder to self - refresh before post )

 

[Ralph M]

Thanks to all, got it.

I have one more (I think). I need to find the 1st Sunday after January 6th. I've been trying to figure out the formula on my own, to no avail.
The year would be in cell "C1". Thanks for any help.

 

[PaddyD]

Book1
	B	C	D	E
1	Date	6/01/2004
2	FollowingSunday:	Sun11/01/2004
Sheet2

formula is:

=DATE(YEAR(C1),MONTH(C1),DAY(C1)+CHOOSE(WEEKDAY(C1),7,6,5,4,3,2,1))

 

[Zack Barresse]

limited testing...

Book14.xls
	A	B	C	D
1	Year	Date	SundayAfter
2	2004	1/6/2004	1/11/2004
Sheet2

 

[Ralph M]

Thanks again everyone, works great.