First Monday in the Year

[lapta301]

Dear All

With the year in B4 (say 2014) could I trouble someone for a formula to show the first Monday in the year.

As ever my thanks

 

[cml19722000]

Get this formula from someone...

=DATE(YEAR(B4),1,8)-WEEKDAY(DATE(YEAR(B4),1,6))

 

[lapta301]

Hi cml19722000


With 2014 in B4 I'm getting 02/01/1905

 

[taps]

Hi,

You can also try below function prepared for you. Put it inside a module and use it as a excel formula from any cell.

=DayDate("Monday",2013,1,1)

You can also use the cell referrence like A1, B1 etc.

Function DayDate(DayToFind As String, yr As Integer, Mn As Integer, Dy As Integer) As String
    Dim Dt As Date
    Dim Dayy As String
    
    Dt = DateValue(yr & "/" & Mn & "/" & Dy)
    Dayy = Format(Dt, "DDDD")
    
    Do While Dayy <> DayToFind
    
    Dy = Dy + 1
    Dt = DateValue(yr & "/" & Mn & "/" & Dy)
    Dayy = Format(Dt, "DDDD")
    
    Loop
    
    DayDate = Format(Dt, "DD-MMM-YYYY")
End Function

Regards
taps

 

[cml19722000]

Hi,

Your input need to be in "Date" format. Thanks.

 

[lapta301]

Taps

Thank you that works a treat

Just in case someone asks for a different first day in a year could you explain to an idiot how this actually works.

Again my thanks

 

[taps]

Hi,

You are most welcome.
The above formula can be used to findout the first date of any days, any year and any month.

Just you need to change the day (Monday...Sunday etc.), the year and the month like below.

=DayDate("Friday",2014,2,1) will return back the first Friday in February in 2014.

Regards
taps

 

[lapta301]

taps

What a real idiot.

I was looking so intently at the code that I forgot the formula

Again my sincere thanks for a very versatile piece of code

 

[Marcol]

There are several ways with standard formula, here's one.
With your year in A2

=DATE(A2,1,1)+CHOOSE(WEEKDAY(DATE(A2,1,1),2),0,6,5,4,3,2,1)

 

[Rick Rothstein]

Just in case someone asks for a different first day in a year could you explain to an idiot how this actually works.

See my mini-blog article here for a general solution for any given month...

Nth Such-And-Such Day Of The Month

The article contains both a formula and code solution.