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

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")

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

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))

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