 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / March 2008days 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. 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 > 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. >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. > >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 [quoted text clipped - 11 lines] > 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. 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. > >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. [quoted text clipped - 5 lines] > 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 > This isn't a calendar month but more of a complete monthly period > beginning [quoted text clipped - 18 lines] >> me to help you. >> --ron It doesn't have to be pretty, just as long as it works. Perfect. Thank you. > I'm guessing these aren't necessarily the most efficient formulas, but they > do appear to work... [quoted text clipped - 31 lines] > >> 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 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. > >This isn't a calendar month but more of a complete monthly period beginning > >with a Monday and ending with a Sunday. [quoted text clipped - 21 lines] > > --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\)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote: >> End of Period: >> [quoted text clipped - 5 lines] > >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 >>> End of Period: >>> [quoted text clipped - 14 lines] > > =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 On Thu, 6 Mar 2008 18:05:50 -0500, "Rick Rothstein \(MVP - VB\)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote: >>>> End of Period: >>>> [quoted text clipped - 19 lines] > >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: >>>>> [quoted text clipped - 25 lines] > 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 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. > > End of Period: > > [quoted text clipped - 5 lines] > > 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 > It works if A1 has the formula date(2008,2,1) and just the month in the > formula changes. [quoted text clipped - 13 lines] >> >> 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. > Are you sure? I'm pretty sure my original test showed it not returning the > correct date for the first of each month. [quoted text clipped - 18 lines] > >> > >> 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. 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. > >The first day of the broadcast month does not always correspond with first of > >the calendar month. [quoted text clipped - 8 lines] > 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. 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. > >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. [quoted text clipped - 18 lines] > =DATE(YEAR(A2),MONTH(A2)+1,7)-WEEKDAY(DATE(YEAR(A2),MONTH(A2)+1,6)) > --ron |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.