I'm looking for formulas that will give me the first Monday and last Sunday
of a period. For example The First Monday of February 2008 would be February
4 and the last Sunday would be March 2. Add x number of days to the formulas
to give me the first Monday in April 2008 as April 7 and the last Sunday as
May 4.

probably too complicated but it seems to work

=A1+(2<WEEKDAY(A1))*(7-WEEKDAY(A1)+2)+(2=WEEKDAY(A1))*(2-WEEKDAY(A1))-7


Your date in A1

Mike

"Bob" wrote:

I'm looking for formulas that will give me the first Monday and last Sunday
of a period. For example The First Monday of February 2008 would be February
4 and the last Sunday would be March 2. Add x number of days to the formulas
to give me the first Monday in April 2008 as April 7 and the last Sunday as
May 4.

On Thu, 6 Mar 2008 11:02:04 -0800, Bob wrote:

I'm looking for formulas that will give me the first Monday and last Sunday
of a period. For example The First Monday of February 2008 would be February
4 and the last Sunday would be March 2. Add x number of days to the formulas
to give me the first Monday in April 2008 as April 7 and the last Sunday as
May 4.

What is your definition of a "period"?

With any date in A1, the first Monday of the month of that date is given by the
formula:

=A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+6)

But I don't know how you are computing the "end of the period".
--ron

I interchanged the word period with month. For example the month of May 2008
would begin with April 28 and end the final Sunday would be June 1.

"Ron Rosenfeld" wrote:

On Thu, 6 Mar 2008 11:02:04 -0800, Bob wrote:

I'm looking for formulas that will give me the first Monday and last Sunday
of a period. For example The First Monday of February 2008 would be February
4 and the last Sunday would be March 2. Add x number of days to the formulas
to give me the first Monday in April 2008 as April 7 and the last Sunday as
May 4.

What is your definition of a "period"?

With any date in A1, the first Monday of the month of that date is given by the
formula:

=A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+6)

But I don't know how you are computing the "end of the period".
--ron

On Thu, 6 Mar 2008 12:42:04 -0800, Bob wrote:

I interchanged the word period with month. For example the month of May 2008
would begin with April 28 and end the final Sunday would be June 1.

Now I am completely confused. I always thought that May began on May 1 and
ended on May 31; and that June and April were not in the month of May.

Your going to have to be more explicit about your calendar rules in order for
me to help you.
--ron

This isn't a calendar month but more of a complete monthly period beginning
with a Monday and ending with a Sunday.
If the calendar month ends on a Tuesday the period would continue to the end
of the week through Sunday. If the calendar month begins after a Monday the
period would begin with the preceding Monday. Also know as a broadcast month.

"Ron Rosenfeld" wrote:

On Thu, 6 Mar 2008 12:42:04 -0800, Bob wrote:

I interchanged the word period with month. For example the month of May 2008
would begin with April 28 and end the final Sunday would be June 1.

Now I am completely confused. I always thought that May began on May 1 and
ended on May 31; and that June and April were not in the month of May.

Your going to have to be more explicit about your calendar rules in order for
me to help you.
--ron

I'm guessing these aren't necessarily the most efficient formulas, but they
do appear to work...

First Monday of the Month
=================================
=DATE(YEAR(A1),MONTH(A1),1)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))-7*(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))=1)+2

Last Sunday of the Month
=================================
=DATE(YEAR(A1),MONTH(A1)+1,7)-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,7))-7*(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,7))=1)+1

Rick


"Bob" wrote in message
...
This isn't a calendar month but more of a complete monthly period
beginning
with a Monday and ending with a Sunday.
If the calendar month ends on a Tuesday the period would continue to the
end
of the week through Sunday. If the calendar month begins after a Monday
the
period would begin with the preceding Monday. Also know as a broadcast
month.

"Ron Rosenfeld" wrote:

On Thu, 6 Mar 2008 12:42:04 -0800, Bob
wrote:

I interchanged the word period with month. For example the month of May
2008
would begin with April 28 and end the final Sunday would be June 1.

Now I am completely confused. I always thought that May began on May 1
and
ended on May 31; and that June and April were not in the month of May.

Your going to have to be more explicit about your calendar rules in order
for
me to help you.
--ron

It doesn't have to be pretty, just as long as it works.

Perfect. Thank you.

"Rick Rothstein (MVP - VB)" wrote:

I'm guessing these aren't necessarily the most efficient formulas, but they
do appear to work...

First Monday of the Month
=================================
=DATE(YEAR(A1),MONTH(A1),1)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))-7*(WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))=1)+2

Last Sunday of the Month
=================================
=DATE(YEAR(A1),MONTH(A1)+1,7)-WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,7))-7*(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,7))=1)+1

Rick


"Bob" wrote in message
...
This isn't a calendar month but more of a complete monthly period
beginning
with a Monday and ending with a Sunday.
If the calendar month ends on a Tuesday the period would continue to the
end
of the week through Sunday. If the calendar month begins after a Monday
the
period would begin with the preceding Monday. Also know as a broadcast
month.

"Ron Rosenfeld" wrote:

On Thu, 6 Mar 2008 12:42:04 -0800, Bob
wrote:

I interchanged the word period with month. For example the month of May
2008
would begin with April 28 and end the final Sunday would be June 1.

Now I am completely confused. I always thought that May began on May 1
and
ended on May 31; and that June and April were not in the month of May.

Your going to have to be more explicit about your calendar rules in order
for
me to help you.
--ron

On Thu, 6 Mar 2008 13:14:07 -0800, Bob wrote:

This isn't a calendar month but more of a complete monthly period beginning
with a Monday and ending with a Sunday.
If the calendar month ends on a Tuesday the period would continue to the end
of the week through Sunday. If the calendar month begins after a Monday the
period would begin with the preceding Monday. Also know as a broadc

I think I have it now.

If I understand you correctly, the beginning of the period is the latest Monday
that is equal to or less the beginning of a Month.

The end of the period is the earliest Sunday that is equal to or greater than
the end of that Month.

With any date in a particular calendar month in A1:

Beginning of Period:

=A1-DAY(A1)+2-WEEKDAY(A1-DAY(A1))

End of Period:

=A1+39-DAY(A1+32)-WEEKDAY(A1+32-DAY(A1+33))

--ron

Just goes to show that there is usually more than one way to accomplish a goal.

"Ron Rosenfeld" wrote:

On Thu, 6 Mar 2008 13:14:07 -0800, Bob wrote:

This isn't a calendar month but more of a complete monthly period beginning
with a Monday and ending with a Sunday.
If the calendar month ends on a Tuesday the period would continue to the end
of the week through Sunday. If the calendar month begins after a Monday the
period would begin with the preceding Monday. Also know as a broadc

I think I have it now.

If I understand you correctly, the beginning of the period is the latest Monday
that is equal to or less the beginning of a Month.

The end of the period is the earliest Sunday that is equal to or greater than
the end of that Month.

With any date in a particular calendar month in A1:

Beginning of Period:

=A1-DAY(A1)+2-WEEKDAY(A1-DAY(A1))

End of Period:

=A1+39-DAY(A1+32)-WEEKDAY(A1+32-DAY(A1+33))

--ron

End of Period:

=A1+39-DAY(A1+32)-WEEKDAY(A1+32-DAY(A1+33))

I do not believe this formula works all the time. Unless I am wrong, it
fails for dates like 2/28/2008 through 3/1/2008, 3/29/2008 through 4/1/2008,
4/29/2008 through 5/1/2008, and so on.

Rick

On Thu, 6 Mar 2008 17:43:20 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

End of Period:

=A1+39-DAY(A1+32)-WEEKDAY(A1+32-DAY(A1+33))

I do not believe this formula works all the time. Unless I am wrong, it
fails for dates like 2/28/2008 through 3/1/2008, 3/29/2008 through 4/1/2008,
4/29/2008 through 5/1/2008, and so on.

Rick

You're correct, and <slap upside the face I've made that same %$#^&$ mistake
before.

Should be:

=A1-DAY(A1)+39-DAY(A1-DAY(A1)+32)-WEEKDAY(A1-DAY(A1)+39-DAY(A1-DAY(A1)+33))

--ron

It works if A1 has the formula date(2008,2,1) and just the month in the
formula changes.

I have link on the date so that the month links to a cell and the user just
adds 1 to the value in the cell.

"Rick Rothstein (MVP - VB)" wrote:

End of Period:

=A1+39-DAY(A1+32)-WEEKDAY(A1+32-DAY(A1+33))

I do not believe this formula works all the time. Unless I am wrong, it
fails for dates like 2/28/2008 through 3/1/2008, 3/29/2008 through 4/1/2008,
4/29/2008 through 5/1/2008, and so on.

Rick

End of Period:

=A1+39-DAY(A1+32)-WEEKDAY(A1+32-DAY(A1+33))

I do not believe this formula works all the time. Unless I am wrong, it
fails for dates like 2/28/2008 through 3/1/2008, 3/29/2008 through
4/1/2008,
4/29/2008 through 5/1/2008, and so on.

Rick

You're correct, and <slap upside the face I've made that same %$#^&$
mistake
before.

Should be:

=A1-DAY(A1)+39-DAY(A1-DAY(A1)+32)-WEEKDAY(A1-DAY(A1)+39-DAY(A1-DAY(A1)+33))

I think there are still problems with that one too. Try the first of each
month (2/1/2008, 3/1/2008, 4/1/2008, etc.)

Rick

Are you sure? I'm pretty sure my original test showed it not returning the
correct date for the first of each month.

Rick


"Bob" wrote in message
...
It works if A1 has the formula date(2008,2,1) and just the month in the
formula changes.

I have link on the date so that the month links to a cell and the user
just
adds 1 to the value in the cell.

"Rick Rothstein (MVP - VB)" wrote:

End of Period:

=A1+39-DAY(A1+32)-WEEKDAY(A1+32-DAY(A1+33))

I do not believe this formula works all the time. Unless I am wrong, it
fails for dates like 2/28/2008 through 3/1/2008, 3/29/2008 through
4/1/2008,
4/29/2008 through 5/1/2008, and so on.

Rick

On Thu, 6 Mar 2008 18:05:50 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

End of Period:

=A1+39-DAY(A1+32)-WEEKDAY(A1+32-DAY(A1+33))

I do not believe this formula works all the time. Unless I am wrong, it
fails for dates like 2/28/2008 through 3/1/2008, 3/29/2008 through
4/1/2008,
4/29/2008 through 5/1/2008, and so on.

Rick

You're correct, and <slap upside the face I've made that same %$#^&$
mistake
before.

Should be:

=A1-DAY(A1)+39-DAY(A1-DAY(A1)+32)-WEEKDAY(A1-DAY(A1)+39-DAY(A1-DAY(A1)+33))

I think there are still problems with that one too. Try the first of each
month (2/1/2008, 3/1/2008, 4/1/2008, etc.)

Rick

I don't see that.

2/1/2008 -- Sunday 3/2/2008, which is the same result as your longer formula
gives.

Given that the OP's definition results in overlapping, I wonder what it is used
for.

--ron

End of Period:

=A1+39-DAY(A1+32)-WEEKDAY(A1+32-DAY(A1+33))

I do not believe this formula works all the time. Unless I am wrong, it
fails for dates like 2/28/2008 through 3/1/2008, 3/29/2008 through
4/1/2008,
4/29/2008 through 5/1/2008, and so on.

Rick

You're correct, and <slap upside the face I've made that same %$#^&$
mistake
before.

Should be:

=A1-DAY(A1)+39-DAY(A1-DAY(A1)+32)-WEEKDAY(A1-DAY(A1)+39-DAY(A1-DAY(A1)+33))

I think there are still problems with that one too. Try the first of each
month (2/1/2008, 3/1/2008, 4/1/2008, etc.)

Rick

I don't see that.

2/1/2008 -- Sunday 3/2/2008, which is the same result as your longer
formula
gives.

Hmm! You are right... they *do* match up. I must have screwed something up
in my initial test bed. Sorry about that.

Rick

The first day of the broadcast month does not always correspond with first of
the calendar month.
The first day of the broadcast month always begins with a Monday (regardless
of the what day of the week the 1st is) and the last day of the month always
ends on a Sunday.
TV scheduling is funny that way.

"Rick Rothstein (MVP - VB)" wrote:

Are you sure? I'm pretty sure my original test showed it not returning the
correct date for the first of each month.

Rick


"Bob" wrote in message
...
It works if A1 has the formula date(2008,2,1) and just the month in the
formula changes.

I have link on the date so that the month links to a cell and the user
just
adds 1 to the value in the cell.

"Rick Rothstein (MVP - VB)" wrote:

End of Period:

=A1+39-DAY(A1+32)-WEEKDAY(A1+32-DAY(A1+33))

I do not believe this formula works all the time. Unless I am wrong, it
fails for dates like 2/28/2008 through 3/1/2008, 3/29/2008 through
4/1/2008,
4/29/2008 through 5/1/2008, and so on.

Rick

On Fri, 7 Mar 2008 08:24:04 -0800, Bob wrote:

The first day of the broadcast month does not always correspond with first of
the calendar month.
The first day of the broadcast month always begins with a Monday (regardless
of the what day of the week the 1st is) and the last day of the month always
ends on a Sunday.
TV scheduling is funny that way.

Bob,

Your "rules", as I understand them, result in some overlap. How does that get
dealt with in the broadcast world?
--ron

the beginning of the month would start as the formula states (first Monday of
a calendar month. The previous month ends the Sunday before that date.

February ended on Sunday, March 2 and March began on Monday, March 3.

March ends on Sunday April 6 and April begins Monday April 7.

"Ron Rosenfeld" wrote:

On Fri, 7 Mar 2008 08:24:04 -0800, Bob wrote:

The first day of the broadcast month does not always correspond with first of
the calendar month.
The first day of the broadcast month always begins with a Monday (regardless
of the what day of the week the 1st is) and the last day of the month always
ends on a Sunday.
TV scheduling is funny that way.

Bob,

Your "rules", as I understand them, result in some overlap. How does that get
dealt with in the broadcast world?
--ron

On Fri, 7 Mar 2008 11:03:00 -0800, Bob wrote:

the beginning of the month would start as the formula states (first Monday of
a calendar month. The previous month ends the Sunday before that date.

February ended on Sunday, March 2 and March began on Monday, March 3.

March ends on Sunday April 6 and April begins Monday April 7.

Those rules are different from your original rules.

In one of your original examples, you indicated the start of May would be April
28. But the first Monday of the calendar Month May is May 5.

If you want to use these rules, you need different formulas:

Again, with some date in the required calendar month in column A

Beginning: =A2-DAY(A2)+8-WEEKDAY(A2-DAY(A2)+6)

End:

=DATE(YEAR(A2),MONTH(A2)+1,7)-WEEKDAY(DATE(YEAR(A2),MONTH(A2)+1,6))
--ron

Right. Thank you.

"Ron Rosenfeld" wrote:

On Fri, 7 Mar 2008 11:03:00 -0800, Bob wrote:

the beginning of the month would start as the formula states (first Monday of
a calendar month. The previous month ends the Sunday before that date.

February ended on Sunday, March 2 and March began on Monday, March 3.

March ends on Sunday April 6 and April begins Monday April 7.

Those rules are different from your original rules.

In one of your original examples, you indicated the start of May would be April
28. But the first Monday of the calendar Month May is May 5.

If you want to use these rules, you need different formulas:

Again, with some date in the required calendar month in column A

Beginning: =A2-DAY(A2)+8-WEEKDAY(A2-DAY(A2)+6)

End:

=DATE(YEAR(A2),MONTH(A2)+1,7)-WEEKDAY(DATE(YEAR(A2),MONTH(A2)+1,6))
--ron