If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
days of the week formulas
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. |
#2
|
|||
|
|||
days of the week formulas
probably too complicated but it seems to work
=A1+(2WEEKDAY(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. |
#3
|
|||
|
|||
days of the week formulas
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 |
#4
|
|||
|
|||
days of the week formulas
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 |
#5
|
|||
|
|||
days of the week formulas
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 |
#6
|
|||
|
|||
days of the week formulas
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 |
#7
|
|||
|
|||
days of the week formulas
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 |
#8
|
|||
|
|||
days of the week formulas
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 |
#9
|
|||
|
|||
days of the week formulas
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 |
#10
|
|||
|
|||
days of the week formulas
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 |
Thread Tools | |
Display Modes | |
|
|