|
Determining the Sunday date of the third "full" weekend of a month
For a given month number (in column A) and year (in column B), I need to
determine the Sunday date of the third "full" weekend. A "full" weekend is defined as one in which a Saturday and Sunday occur within the same month (i.e., Saturday and Sunday do NOT straddle two months). For example, the Sunday date of the third "full" weekend in November 2009 is November 22, whereas the Sunday date of the third "full" weekend in December 2009 is December 20. I would greatly appreciate any help in coming up with the formula (and worksheet function) that will accurately and consistently determine the Sunday date of the third "full" weekend of a month. Thanks, Bob |
Determining the Sunday date of the third "full" weekend of a month
Give this formula a try...
=DATE(B1,A1,22)-WEEKDAY(DATE(B1,A1,7))+7*(DAY(DATE(B1,A1,8)-WEEKDAY(DATE(B1,A1,7)))=1) -- Rick (MVP - Excel) "Bob" wrote in message ... For a given month number (in column A) and year (in column B), I need to determine the Sunday date of the third "full" weekend. A "full" weekend is defined as one in which a Saturday and Sunday occur within the same month (i.e., Saturday and Sunday do NOT straddle two months). For example, the Sunday date of the third "full" weekend in November 2009 is November 22, whereas the Sunday date of the third "full" weekend in December 2009 is December 20. I would greatly appreciate any help in coming up with the formula (and worksheet function) that will accurately and consistently determine the Sunday date of the third "full" weekend of a month. Thanks, Bob |
Determining the Sunday date of the third "full" weekend of a month
Another one (a bit shorter)
=DATE(B1,A1,22)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))+1 If this post helps click Yes --------------- Jacob Skaria "Bob" wrote: For a given month number (in column A) and year (in column B), I need to determine the Sunday date of the third "full" weekend. A "full" weekend is defined as one in which a Saturday and Sunday occur within the same month (i.e., Saturday and Sunday do NOT straddle two months). For example, the Sunday date of the third "full" weekend in November 2009 is November 22, whereas the Sunday date of the third "full" weekend in December 2009 is December 20. I would greatly appreciate any help in coming up with the formula (and worksheet function) that will accurately and consistently determine the Sunday date of the third "full" weekend of a month. Thanks, Bob |
Determining the Sunday date of the third "full" weekend of a m
Oops.. correction..
=DATE(B1,A1,22)-WEEKDAY(DATE(B1,A1,1))+1 If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Another one (a bit shorter) =DATE(B1,A1,22)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))+1 If this post helps click Yes --------------- Jacob Skaria "Bob" wrote: For a given month number (in column A) and year (in column B), I need to determine the Sunday date of the third "full" weekend. A "full" weekend is defined as one in which a Saturday and Sunday occur within the same month (i.e., Saturday and Sunday do NOT straddle two months). For example, the Sunday date of the third "full" weekend in November 2009 is November 22, whereas the Sunday date of the third "full" weekend in December 2009 is December 20. I would greatly appreciate any help in coming up with the formula (and worksheet function) that will accurately and consistently determine the Sunday date of the third "full" weekend of a month. Thanks, Bob |
Determining the Sunday date of the third "full" weekend of a month
Here is a shorter version...
=DATE(B1,A1,22)-WEEKDAY(DATE(B1,A1,7))+7*(WEEKDAY(DATE(B1,A1,1))=1 ) -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Give this formula a try... =DATE(B1,A1,22)-WEEKDAY(DATE(B1,A1,7))+7*(DAY(DATE(B1,A1,8)-WEEKDAY(DATE(B1,A1,7)))=1) -- Rick (MVP - Excel) "Bob" wrote in message ... For a given month number (in column A) and year (in column B), I need to determine the Sunday date of the third "full" weekend. A "full" weekend is defined as one in which a Saturday and Sunday occur within the same month (i.e., Saturday and Sunday do NOT straddle two months). For example, the Sunday date of the third "full" weekend in November 2009 is November 22, whereas the Sunday date of the third "full" weekend in December 2009 is December 20. I would greatly appreciate any help in coming up with the formula (and worksheet function) that will accurately and consistently determine the Sunday date of the third "full" weekend of a month. Thanks, Bob |
Determining the Sunday date of the third "full" weekend of a month
Use Jacob's formula... it is shorter and calls less functions than mine.
-- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Here is a shorter version... =DATE(B1,A1,22)-WEEKDAY(DATE(B1,A1,7))+7*(WEEKDAY(DATE(B1,A1,1))=1 ) -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Give this formula a try... =DATE(B1,A1,22)-WEEKDAY(DATE(B1,A1,7))+7*(DAY(DATE(B1,A1,8)-WEEKDAY(DATE(B1,A1,7)))=1) -- Rick (MVP - Excel) "Bob" wrote in message ... For a given month number (in column A) and year (in column B), I need to determine the Sunday date of the third "full" weekend. A "full" weekend is defined as one in which a Saturday and Sunday occur within the same month (i.e., Saturday and Sunday do NOT straddle two months). For example, the Sunday date of the third "full" weekend in November 2009 is November 22, whereas the Sunday date of the third "full" weekend in December 2009 is December 20. I would greatly appreciate any help in coming up with the formula (and worksheet function) that will accurately and consistently determine the Sunday date of the third "full" weekend of a month. Thanks, Bob |
Determining the Sunday date of the third "full" weekend of a m
=DATE(B1,A1,22)-WEEKDAY(DATE(B1,A1,1))+1
For Feb 2009 that formula returns 2/22/2009 which is the 4th Sunday of the month. The 3rd weekday of a month will always be <=21st of the month. Try this: =DATE(B1,A1,22)-WEEKDAY(DATE(B1,A1,7)) The general formula for the nth weekday of a month is: DATE(year,month,1+n*7)-WEEKDAY(DATE(year,month,8-dow)) Whe n = nth weekday = a number from 1 to 5 (for the 3rd Sunday n = 3) dow = a number from 1 to 7, Sunday =1 through Saturday = 7 -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... Oops.. correction.. =DATE(B1,A1,22)-WEEKDAY(DATE(B1,A1,1))+1 If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Another one (a bit shorter) =DATE(B1,A1,22)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))+1 If this post helps click Yes --------------- Jacob Skaria "Bob" wrote: For a given month number (in column A) and year (in column B), I need to determine the Sunday date of the third "full" weekend. A "full" weekend is defined as one in which a Saturday and Sunday occur within the same month (i.e., Saturday and Sunday do NOT straddle two months). For example, the Sunday date of the third "full" weekend in November 2009 is November 22, whereas the Sunday date of the third "full" weekend in December 2009 is December 20. I would greatly appreciate any help in coming up with the formula (and worksheet function) that will accurately and consistently determine the Sunday date of the third "full" weekend of a month. Thanks, Bob |
Determining the Sunday date of the third "full" weekend of a m
Biff; the OP is looking for a "full" weekend which the OP has defined as one
in which a Saturday and Sunday occur within the same month.. If this post helps click Yes --------------- Jacob Skaria "T. Valko" wrote: =DATE(B1,A1,22)-WEEKDAY(DATE(B1,A1,1))+1 For Feb 2009 that formula returns 2/22/2009 which is the 4th Sunday of the month. The 3rd weekday of a month will always be <=21st of the month. Try this: =DATE(B1,A1,22)-WEEKDAY(DATE(B1,A1,7)) The general formula for the nth weekday of a month is: DATE(year,month,1+n*7)-WEEKDAY(DATE(year,month,8-dow)) Whe n = nth weekday = a number from 1 to 5 (for the 3rd Sunday n = 3) dow = a number from 1 to 7, Sunday =1 through Saturday = 7 -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... Oops.. correction.. =DATE(B1,A1,22)-WEEKDAY(DATE(B1,A1,1))+1 If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Another one (a bit shorter) =DATE(B1,A1,22)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))+1 If this post helps click Yes --------------- Jacob Skaria "Bob" wrote: For a given month number (in column A) and year (in column B), I need to determine the Sunday date of the third "full" weekend. A "full" weekend is defined as one in which a Saturday and Sunday occur within the same month (i.e., Saturday and Sunday do NOT straddle two months). For example, the Sunday date of the third "full" weekend in November 2009 is November 22, whereas the Sunday date of the third "full" weekend in December 2009 is December 20. I would greatly appreciate any help in coming up with the formula (and worksheet function) that will accurately and consistently determine the Sunday date of the third "full" weekend of a month. Thanks, Bob |
Determining the Sunday date of the third "full" weekend of a m
Jacob's formula is correct... the OP asked for the Sunday date for the 3rd
FULL weekend... both Saturday and Sunday being within the current month. February 1, 2009 occurs on a Sunday which means its companion Saturday is in the previous month; hence, that first Sunday is not part of a full weekend and is not counted. -- Rick (MVP - Excel) "T. Valko" wrote in message ... =DATE(B1,A1,22)-WEEKDAY(DATE(B1,A1,1))+1 For Feb 2009 that formula returns 2/22/2009 which is the 4th Sunday of the month. The 3rd weekday of a month will always be <=21st of the month. Try this: =DATE(B1,A1,22)-WEEKDAY(DATE(B1,A1,7)) The general formula for the nth weekday of a month is: DATE(year,month,1+n*7)-WEEKDAY(DATE(year,month,8-dow)) Whe n = nth weekday = a number from 1 to 5 (for the 3rd Sunday n = 3) dow = a number from 1 to 7, Sunday =1 through Saturday = 7 -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... Oops.. correction.. =DATE(B1,A1,22)-WEEKDAY(DATE(B1,A1,1))+1 If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Another one (a bit shorter) =DATE(B1,A1,22)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))+1 If this post helps click Yes --------------- Jacob Skaria "Bob" wrote: For a given month number (in column A) and year (in column B), I need to determine the Sunday date of the third "full" weekend. A "full" weekend is defined as one in which a Saturday and Sunday occur within the same month (i.e., Saturday and Sunday do NOT straddle two months). For example, the Sunday date of the third "full" weekend in November 2009 is November 22, whereas the Sunday date of the third "full" weekend in December 2009 is December 20. I would greatly appreciate any help in coming up with the formula (and worksheet function) that will accurately and consistently determine the Sunday date of the third "full" weekend of a month. Thanks, Bob |
Determining the Sunday date of the third "full" weekend of a m
Ok, I just re-read the original post. I see now what they meant by full
weekend. -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... Biff; the OP is looking for a "full" weekend which the OP has defined as one in which a Saturday and Sunday occur within the same month.. If this post helps click Yes --------------- Jacob Skaria "T. Valko" wrote: =DATE(B1,A1,22)-WEEKDAY(DATE(B1,A1,1))+1 For Feb 2009 that formula returns 2/22/2009 which is the 4th Sunday of the month. The 3rd weekday of a month will always be <=21st of the month. Try this: =DATE(B1,A1,22)-WEEKDAY(DATE(B1,A1,7)) The general formula for the nth weekday of a month is: DATE(year,month,1+n*7)-WEEKDAY(DATE(year,month,8-dow)) Whe n = nth weekday = a number from 1 to 5 (for the 3rd Sunday n = 3) dow = a number from 1 to 7, Sunday =1 through Saturday = 7 -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... Oops.. correction.. =DATE(B1,A1,22)-WEEKDAY(DATE(B1,A1,1))+1 If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Another one (a bit shorter) =DATE(B1,A1,22)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))+1 If this post helps click Yes --------------- Jacob Skaria "Bob" wrote: For a given month number (in column A) and year (in column B), I need to determine the Sunday date of the third "full" weekend. A "full" weekend is defined as one in which a Saturday and Sunday occur within the same month (i.e., Saturday and Sunday do NOT straddle two months). For example, the Sunday date of the third "full" weekend in November 2009 is November 22, whereas the Sunday date of the third "full" weekend in December 2009 is December 20. I would greatly appreciate any help in coming up with the formula (and worksheet function) that will accurately and consistently determine the Sunday date of the third "full" weekend of a month. Thanks, Bob |
Determining the Sunday date of the third "full" weekend of a m
Even shorter
=23-WEEKDAY(DATE(B13,A13,1),1) Alan Lloyd |
Determining the Sunday date of the third "full" weekend of a m
I was under the impression the OP wanted the date for the 3rd full Sunday of
the month, not the day number. -- Rick (MVP - Excel) wrote in message ... Even shorter =23-WEEKDAY(DATE(B13,A13,1),1) Alan Lloyd |
Determining the Sunday date of the third "full" weekend of a m
On Sep 8, 8:52�am, "Rick Rothstein"
wrote: I was under the impression the OP wanted the date for the 3rd full Sunday of the month, not the day number. OOOPPPSSS - you're quite right <g Mine should be . . . =DATE(B13,A13,23-WEEKDAY(DATE(B13,A13,1),1)) Much the same as Jacob's Alan Lloyd |
Determining the Sunday date of the third "full" weekend of a m
Rick,
Thanks for your help. I really appreciate it. Bob "Rick Rothstein" wrote: Here is a shorter version... =DATE(B1,A1,22)-WEEKDAY(DATE(B1,A1,7))+7*(WEEKDAY(DATE(B1,A1,1))=1 ) -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Give this formula a try... =DATE(B1,A1,22)-WEEKDAY(DATE(B1,A1,7))+7*(DAY(DATE(B1,A1,8)-WEEKDAY(DATE(B1,A1,7)))=1) -- Rick (MVP - Excel) "Bob" wrote in message ... For a given month number (in column A) and year (in column B), I need to determine the Sunday date of the third "full" weekend. A "full" weekend is defined as one in which a Saturday and Sunday occur within the same month (i.e., Saturday and Sunday do NOT straddle two months). For example, the Sunday date of the third "full" weekend in November 2009 is November 22, whereas the Sunday date of the third "full" weekend in December 2009 is December 20. I would greatly appreciate any help in coming up with the formula (and worksheet function) that will accurately and consistently determine the Sunday date of the third "full" weekend of a month. Thanks, Bob |
Determining the Sunday date of the third "full" weekend of a m
Jacob,
Thanks for your elegant and concise formula! Bob "Jacob Skaria" wrote: Oops.. correction.. =DATE(B1,A1,22)-WEEKDAY(DATE(B1,A1,1))+1 If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Another one (a bit shorter) =DATE(B1,A1,22)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1))+1 If this post helps click Yes --------------- Jacob Skaria "Bob" wrote: For a given month number (in column A) and year (in column B), I need to determine the Sunday date of the third "full" weekend. A "full" weekend is defined as one in which a Saturday and Sunday occur within the same month (i.e., Saturday and Sunday do NOT straddle two months). For example, the Sunday date of the third "full" weekend in November 2009 is November 22, whereas the Sunday date of the third "full" weekend in December 2009 is December 20. I would greatly appreciate any help in coming up with the formula (and worksheet function) that will accurately and consistently determine the Sunday date of the third "full" weekend of a month. Thanks, Bob |
Determining the Sunday date of the third "full" weekend of amonth
Hello,
I suggest =FLOOR(DATE(B1,A1,21),7)+1 Regards, Bernd |
Determining the Sunday date of the third "full" weekend of a month
On Mon, 7 Sep 2009 20:24:01 -0700, Bob wrote:
For a given month number (in column A) and year (in column B), I need to determine the Sunday date of the third "full" weekend. A "full" weekend is defined as one in which a Saturday and Sunday occur within the same month (i.e., Saturday and Sunday do NOT straddle two months). For example, the Sunday date of the third "full" weekend in November 2009 is November 22, whereas the Sunday date of the third "full" weekend in December 2009 is December 20. I would greatly appreciate any help in coming up with the formula (and worksheet function) that will accurately and consistently determine the Sunday date of the third "full" weekend of a month. Thanks, Bob With some date in the month of interest in A1: =A1-DAY(A1)+23-WEEKDAY(A1-DAY(A1)+1) --ron |
Determining the Sunday date of the third "full" weekend of a month
On Mon, 7 Sep 2009 20:24:01 -0700, Bob wrote:
For a given month number (in column A) and year (in column B), I need to determine the Sunday date of the third "full" weekend. A "full" weekend is defined as one in which a Saturday and Sunday occur within the same month (i.e., Saturday and Sunday do NOT straddle two months). For example, the Sunday date of the third "full" weekend in November 2009 is November 22, whereas the Sunday date of the third "full" weekend in December 2009 is December 20. I would greatly appreciate any help in coming up with the formula (and worksheet function) that will accurately and consistently determine the Sunday date of the third "full" weekend of a month. Thanks, Bob If you must have the month number in A1 and the year in B1, then: =DATE(B1,A1,23)-WEEKDAY(DATE(B1,A1,1)) --ron |
Determining the Sunday date of the third "full" weekend of a m
Ron,
Thanks for your help. I greatly appreciate it. Bob "Ron Rosenfeld" wrote: On Mon, 7 Sep 2009 20:24:01 -0700, Bob wrote: For a given month number (in column A) and year (in column B), I need to determine the Sunday date of the third "full" weekend. A "full" weekend is defined as one in which a Saturday and Sunday occur within the same month (i.e., Saturday and Sunday do NOT straddle two months). For example, the Sunday date of the third "full" weekend in November 2009 is November 22, whereas the Sunday date of the third "full" weekend in December 2009 is December 20. I would greatly appreciate any help in coming up with the formula (and worksheet function) that will accurately and consistently determine the Sunday date of the third "full" weekend of a month. Thanks, Bob If you must have the month number in A1 and the year in B1, then: =DATE(B1,A1,23)-WEEKDAY(DATE(B1,A1,1)) --ron |
Determining the Sunday date of the third "full" weekend of a m
Wow! What a great formula!
While I understand how the FLOOR function works in general, I can't seem to figure out how it works when used with a date, as in your formula. Thanks for your help. Regards, Bob "Bernd P" wrote: Hello, I suggest =FLOOR(DATE(B1,A1,21),7)+1 Regards, Bernd |
Determining the Sunday date of the third "full" weekend of a m
Hello Bob,
Dates are just integer numbers in Excel. For a general solution of first (or last) workdays of a given month see http://sulprobil.com/html/weekday_in_month.html please. Regards, Bernd |
Determining the Sunday date of the third "full" weekend of a m
Hi Bernd,
Sorry, I should have been more specific in my comment. While I am aware that dates are just integers, I still do not understand how the FLOOR's "Significance" parameter works when used with dates. Can you elaborate? Thanks, Bob "Bernd P" wrote: Hello Bob, Dates are just integer numbers in Excel. For a general solution of first (or last) workdays of a given month see http://sulprobil.com/html/weekday_in_month.html please. Regards, Bernd |
Determining the Sunday date of the third "full" weekend of a m
Hello Bob,
FLOOR(x,7) rounds x down to the next number divisible by 7. For non- negative numbers it is the same as x-MOD(x,7). I just use it as a short cut. Regards, Bernd |
Determining the Sunday date of the third "full" weekend of amonth
Hello Barry,
I do not support the 1904 date system. Please see entry #7 of my Excel Don'ts: http://sulprobil.com/html/excel_don_ts.html Regards, Bernd |
Determining the Sunday date of the third "full" weekend of a m
Bernd,
Thanks for the additional info, and for all your help. Regards, Bob "Bernd P" wrote: Hello Bob, FLOOR(x,7) rounds x down to the next number divisible by 7. For non- negative numbers it is the same as x-MOD(x,7). I just use it as a short cut. Regards, Bernd |
Determining the Sunday date of the third "full" weekend of a m
Hello Bob,
You are welcome. Thanks for your feedback. Regards, Bernd |
| All times are GMT +1. The time now is 12:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com