First Monday in the Year

Thanks:  0
Likes:  0

# Thread: First Monday in the Year

1. ## First Monday in the Year

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

2. ## Re: First Monday in the Year

Get this formula from someone...

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

3. ## Re: First Monday in the Year

Hi cml19722000

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

4. ## Re: First Monday in the Year

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.

Code:
```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

5. ## Re: First Monday in the Year

Hi,

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

6. ## Re: First Monday in the Year

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

7. ## Re: First Monday in the Year

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

8. ## Re: First Monday in the Year

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

9. ## Re: First Monday in the Year

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

10. ## Re: First Monday in the Year

Originally Posted by lapta301
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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•