Date of the first Monday of a month?

The answer is "Yes" there is a formula. If you provide more details regarding what you want we will be able to give you a direct answer.... otherwise we can provide general solutions and you can adapt them as needed.:

Here is a first try

A8=DAYNAME($B$1&"/"&B8&"/"&$B$2)

fill down to A8 down to A14

C3=VLOOKUP(B3, $A$8:$B$14, 2)

Input data is B1 (the month), B2 (the year), and B3 the name of the weekday you want the date of.

C3 is the "output" (the date, in this case, of the 1st Monday in Oct 2011)

Expanding the concept, we can make a table to list the dates of the first occurrence of any day of the week,

In this example, B1 contains: =TODAY()

B2 contains:

=WORKDAY(EOMONTH(B1,-1)-WEEKDAY(EOMONTH(B1,-1),3),1)+6

B3 contains:

=IF(DAY(B2)>6, B2-6, B2+1)

Fill down from B3 to B8.

That should take care of your need for any publication date.

Jerry

Zems wrote:

Thanks for that. It gives me an idea where to start.

I have a new spreadsheet each month for weekly newspapers with entries going in for the day of publication (usually Tuesday or Wednesday). At the moment I use the formula

=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))

I then paste the value on the first Tuesday and use that date to work out the date for the rest of the Tuesdays in the month. If I miss the day then I have to do it manually. I was just trying to work out a formula that would give me the date of that first day. Hope that makes sense.

Zems,

Your =DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())) will give the same result as =TODAY()

There have been discussions here on finding the dates of the first weekdays of the month, which you can search for and review. Barry gave what I believe is the most elegant solution in his response on 08 Apr this year.

Barry's solution is essentially as follows:

If A1 contains =TODAY(), the following formula will give the first Monday of the current month:

=WORKDAY(EOMONTH(A1,-1)-WEEKDAY(EOMONTH(A1,-1),3),1)+6

Regards,

Jerry

Zems wrote:

Just been having another think. Is there any advantage in having =TODAY() in its own cell instead of in the formula :

=WORKDAY(EOMONTH(TODAY(),-1)-WEEKDAY(EOMONTH(TODAY(),-1),3),1)+6

For me, yes there is an advantage. I like to be able to see the intermediate data whenever it is practical to display it. Putting it in its own cell allows me to have more comfort with the result.

Jerry

If you post an example it will be much easier to assist you