Find the first Sunday for a given year?

[Guest]

I am looking for a way to find the first weekday of the year, say the first
Sunday or Monday, while giving only the year to excel. For example, if I
type in 2006, I want it to automatically give me the date of the first Monday
of the year. Is there any function or combination of functions that will do
this for me?

 

[vezerid]

Eutrapelia,
(nice nickname btw, I trust you have the idea of its meaning in its
original language )

Assume the year is in A1 and in B1 you put a number from 1(Sunday) to
7(Saturday). The following formula will return the first day of your B1
choice:
=DATE(A1, 1, MOD(7-DATE(A1, 1, 1)+ B1,7)+1)

HTH
Kostis Vezerides

 

[Ron Rosenfeld]

I am looking for a way to find the first weekday of the year, say the first
Sunday or Monday, while giving only the year to excel. For example, if I
type in 2006, I want it to automatically give me the date of the first Monday
of the year. Is there any function or combination of functions that will do
this for me?

With YEAR in A1:

First Sunday:

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

First Monday:

=DATE(A1,1,8)-WEEKDAY(DATE(A1,1,6))

First Weekday:

If you have the Analysis ToolPak installed:

=workday(DATE(A1,1,0),1)

(See HELP for the workday function for instructions on how to install it).

If you do not have the ATP installed:

=DATE(A1,1,0)+CHOOSE(WEEKDAY(DATE(A1,1,0)),1,1,1,1,1,3,2)


--ron