First Monday of every month

[PCTech]

I have 12 Spreadsheets, one for each month. I need a formula that will give me the fist Monday of the month of the current year. If I have to put January, or whatever month, in a cell somewhere, I can.
Thanks in advance.

 

[Jonmo1]

Which version of Excel are you using?

 

[PCTech]

2106 0365

 

[Jonmo1]

Try this, with the text string January in A1

=WORKDAY.INTL((A1&1)-1,1,"0111111")

 

[Rick Rothstein]

I have 12 Spreadsheets, one for each month. I need a formula that will give me the fist Monday of the month of the current year. If I have to put January, or whatever month, in a cell somewhere, I can.

Assuming you have put the name of the month (Jan or January, Feb or February, etc.) in cell A1, then this formula will return the date serial number for the first Monday of that month for the current year...

=DATE(YEAR(NOW()),MONTH(1&A1),8)-WEEKDAY(DATE(YEAR(NOW()),MONTH(1&A1),6))

This formula should work on all versions of Excel, even those versions before when the WORKDAY.INTL function was introduced (which Jonmo1 showed you in Message #4).

You can see the general template formula I used for this formula in my mini-blog article here...

<!-- title / author block --> [h=3]Nth Such-And-Such Day Of The Month[/h]

 

[PCTech]

Thanks, I ended up using Jonno1's method. I can follow the binary code in it. So Tuesday is 1011111 and Wednesday is 1101111 etc. And the week is easily changed by changing -1,1 to -1,2. And I put a lot of thought into how to handle the 8th because that is part of the next week by using
=IF(WORKDAY.INTL(($A$1&1)-1,1,"1110111")>WORKDAY.INTL(($A$1&1)-1,1,"0111111"),WORKDAY.INTL(($A$1&1)-1,1,"1110111"),WORKDAY.INTL(($A$1&1)-1,2,"1110111")).
After a few hours of copy and paste and changing 1's and 0's, I figured out that in C1 I could put =B1+1 and in D1 I have = B1+2 and so on. I have a tendency to over think things.

 

[Jonmo1]

You're welcome.