Get this formula from someone...
=DATE(YEAR(B4),1,8)-WEEKDAY(DATE(YEAR(B4),1,6))
This is a discussion on First Monday in the Year within the Excel Questions forums, part of the Question Forums category; Dear All With the year in B4 (say 2014) could I trouble someone for a formula to show the first ...
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
Get this formula from someone...
=DATE(YEAR(B4),1,8)-WEEKDAY(DATE(YEAR(B4),1,6))
Hi cml19722000
With 2014 in B4 I'm getting 02/01/1905
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.
RegardsCode: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
taps
Hi,
Your input need to be in "Date" format. Thanks.
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
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
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
There are several ways with standard formula, here's one.
With your year in A2
Code:=DATE(A2,1,1)+CHOOSE(WEEKDAY(DATE(A2,1,1),2),0,6,5,4,3,2,1)
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.
Rick's "mini" blog... http://www.excelfox.com/forum/f22/
.
Want to post a small screen shot? Try MrExcel HTML Maker
How To Use MrExcel HTML Maker: http://www.mrexcel.com/forum/about-b...ml#post2545970
Like this thread? Share it with others