|
| 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 |
|
#11
March 6th, 2008, 10:43 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
days of the week formulas
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 
|
|
#12
March 6th, 2008, 10:58 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
days of the week formulas
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
|
|
#13
March 6th, 2008, 11:04 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
days of the week formulas
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
|
|
#14
March 6th, 2008, 11:05 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
days of the week formulas
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
|
|
#15
March 6th, 2008, 11:10 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
days of the week formulas
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
|
|
#16
March 6th, 2008, 11:20 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
days of the week formulas
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
|
|
#17
March 7th, 2008, 01:21 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
days of the week formulas
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
|
|
#18
March 7th, 2008, 04:24 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
days of the week formulas
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
|
|
#19
March 7th, 2008, 05:31 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
days of the week formulas
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
|
|
#20
March 7th, 2008, 07:03 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
days of the week formulas
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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
