Cell formula for month & day of 1st Sunday of year

[mikeburg]

Needing a cell formula that returns the month & day of the 1st Sunday for a year that has been entered in cell B1.

For example:

With 2007 in B1, need formula in E3 to return 1/07

With 2006 in B1, need formula in E3 to return 1/01

With 2005 in B1, need formula in E3 to return 1/02

etc.

Thanks a lot for all your help. mikeburg

[daddylonglegs]

Try this formula in E3

=DATE(B1,1,8)-WEEKDAY(DATE(B1,1,7))

format as desired, e.g. for your example m/dd

or for a text result....

=TEXT(DATE(B1,1,8)-WEEKDAY(DATE(B1,1,7)),"m/dd")

[mikeburg]

Wow!. Thanks a million, it works great!

I prefer the 1st formula. I can't understand why it works I am so new to this. If you can, will you explain it. I tried looking in Excel help & follow only part of it.

mikeburg

[daddylonglegs]

IF A2 contains any date then because WEEKDAY(A2) will give 1 when A2 is a Sunday, 2 when A2 is Monday etc. it should be clear that

=A2-WEEKDAY(A2)

will always give the previous Saturday to A2....but you can adjust this basic formula to return any day of the week you require, e.g. WEEKDAY(A2-1) will give a 1 when A2 is a Monday, 2 if it's a Tuesday etc. so

=A2-WEEKDAY(A2-1) will always give the previous Sunday to A2.

In your case if you find the Sunday previous to the 8th of January then that will be the first Sunday of the year so if you replace A2 with the 8th of January for your given year you get

=DATE(B1,1,8)-WEEKDAY(DATE(B1,1,8)-1)

which is the same as

=DATE(B1,1,8)-WEEKDAY(DATE(B1,1,7))

[mikeburg]

Thank you for the explanations. I will study this. You are correct that I needed this formula for many applications.

Thank you so very much. mikeburg