Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Saturday of each month(check on Monday)
Hi all,
What formula or code can return a boolean value that I perform a check on MONDAY to see if the coming Saturday is the last Saturday of this month? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Saturday of each month(check on Monday)
I couldn't get eomonth() function working on my computer so I took the 1st of
the next month and subtracted one to get last day of month. What you ned is the difference between the last day of the month and the current date is greater or equal to 5 and less than 12. if it is Monday, there are 5 days between Monday and Saturday. If the difference between the last day of month and the current date is less than 5 there is no Saturday. If the difference between the last date of the month and the currrent date is 12 or more there is two Saturdays until the end of the month. EOMONTH() is equivalent to DATE(YEAR(NOW()),MONTH(NOW())+1,1)-1 =IF(AND((DATE(YEAR(NOW()),MONTH(NOW())+1,1)-1)-TODAY()=5,(DATE(YEAR(NOW()),MONTH(NOW())+1,1)-1)-TODAY()<12),TRUE,FALSE) If EOMONTH() works on your computers then =IF(AND(EOMONTH()-TODAY()=5,EOMONTH()-TODAY()<12),TRUE,FALSE) "jimmy" wrote: Hi all, What formula or code can return a boolean value that I perform a check on MONDAY to see if the coming Saturday is the last Saturday of this month? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Saturday of each month(check on Monday)
Thanks Joel, I also can't use EOMONTH(), but your formula fit my need. ^^
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Saturday of each month(check on Monday)
On Wed, 9 May 2007 18:24:04 +0800, "jimmy" wrote:
Hi all, What formula or code can return a boolean value that I perform a check on MONDAY to see if the coming Saturday is the last Saturday of this month? Thanks With ANY date in A1, the following will return TRUE if the NEXT Saturday is the LAST Saturday in the month; otherwise it will return FALSE. =AND((EOMONTH(A1,0)-WEEKDAY(EOMONTH(A1,0))-A1)<=7, (EOMONTH(A1,0)-WEEKDAY(EOMONTH(A1,0))-A1)0) If the EOMONTH function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in. How? On the Tools menu, click Add-Ins. In the Add-Ins available list, select the Analysis ToolPak box, and then click OK. If necessary, follow the instructions in the setup program If you do not want to add the ATP, then use this formula instead: =AND((DATE(YEAR(A1),MONTH(A1)+1,0)-WEEKDAY( DATE(YEAR(A1),MONTH(A1)+1,0))-A1)<=7,(DATE( YEAR(A1),MONTH(A1)+1,0)-WEEKDAY(DATE( YEAR(A1),MONTH(A1)+1,0))-A1)0) --ron |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Saturday of each month(check on Monday)
On Wed, 9 May 2007 04:19:00 -0700, Joel wrote:
I couldn't get eomonth() function working on my computer so I took the 1st of the next month and subtracted one to get last day of month. What you ned is the difference between the last day of the month and the current date is greater or equal to 5 and less than 12. if it is Monday, there are 5 days between Monday and Saturday. If the difference between the last day of month and the current date is less than 5 there is no Saturday. If the difference between the last date of the month and the currrent date is 12 or more there is two Saturdays until the end of the month. EOMONTH() is equivalent to DATE(YEAR(NOW()),MONTH(NOW())+1,1)-1 or, more simply: DATE(YEAR(NOW()),MONTH(NOW()),0) =IF(AND((DATE(YEAR(NOW()),MONTH(NOW())+1,1)-1)-TODAY()=5,(DATE(YEAR(NOW()),MONTH(NOW())+1,1)-1)-TODAY()<12),TRUE,FALSE) I note some inconsistencies in your formula. I substituted A1 for NOW() and TODAY() in your formula. If A1 contains the Last Saturday of the month, your formula sometimes returns TRUE and sometimes returns FALSE. If the OP only looks at the cell on MONDAY, then it makes no difference. --ron |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Saturday of each month(check on Monday)
ron: your equeation is wrong becaue you didn't consider there is 5 days from
Monday to Saturday. If the end of the month is the 30th and is on Friday, then Monday would be the 26th. The formula would need to be False. The previous Monday the 19th would be True. "Ron Rosenfeld" wrote: On Wed, 9 May 2007 18:24:04 +0800, "jimmy" wrote: Hi all, What formula or code can return a boolean value that I perform a check on MONDAY to see if the coming Saturday is the last Saturday of this month? Thanks With ANY date in A1, the following will return TRUE if the NEXT Saturday is the LAST Saturday in the month; otherwise it will return FALSE. =AND((EOMONTH(A1,0)-WEEKDAY(EOMONTH(A1,0))-A1)<=7, (EOMONTH(A1,0)-WEEKDAY(EOMONTH(A1,0))-A1)0) If the EOMONTH function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in. How? On the Tools menu, click Add-Ins. In the Add-Ins available list, select the Analysis ToolPak box, and then click OK. If necessary, follow the instructions in the setup program If you do not want to add the ATP, then use this formula instead: =AND((DATE(YEAR(A1),MONTH(A1)+1,0)-WEEKDAY( DATE(YEAR(A1),MONTH(A1)+1,0))-A1)<=7,(DATE( YEAR(A1),MONTH(A1)+1,0)-WEEKDAY(DATE( YEAR(A1),MONTH(A1)+1,0))-A1)0) --ron |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Saturday of each month(check on Monday)
Thanks Ron, but it got mistake on June 2007. ^^
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Saturday of each month(check on Monday)
On Wed, 9 May 2007 23:08:46 +0800, "jimmy" wrote:
Thanks Ron, but it got mistake on June 2007. ^^ Yes, you're correct. I was calculating the last Saturday incorrectly. This should work: =AND((DATE(YEAR(A1),MONTH(A1)+1,1)-WEEKDAY( DATE(YEAR(A1),MONTH(A1)+1,1))-A1)<=7,(DATE( YEAR(A1),MONTH(A1)+1,1)-WEEKDAY(DATE( YEAR(A1),MONTH(A1)+1,1))-A1)0) --ron |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Saturday of each month(check on Monday)
On Wed, 9 May 2007 07:14:00 -0700, Joel wrote:
ron: your equeation is wrong becaue you didn't consider there is 5 days from Monday to Saturday. If the end of the month is the 30th and is on Friday, then Monday would be the 26th. The formula would need to be False. The previous Monday the 19th would be True. Actually, my equation was wrong because I was calculating the last Saturday incorrectly. This one should work: =AND((DATE(YEAR(A1),MONTH(A1)+1,1)-WEEKDAY( DATE(YEAR(A1),MONTH(A1)+1,1))-A1)<=7,(DATE( YEAR(A1),MONTH(A1)+1,1)-WEEKDAY(DATE( YEAR(A1),MONTH(A1)+1,1))-A1)0) --ron |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Saturday of each month(check on Monday)
Yup, the previous one didn't work if the month end is SAT..
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Saturday of each month(check on Monday)
Hi,
With ANY date in A1, the following will return TRUE if the NEXT Saturday is the LAST Saturday in the month; otherwise it will return FALSE. ... If you do not want to add the ATP, then use this formula instead: =AND((DATE(YEAR(A1),MONTH(A1)+1,0)-WEEKDAY( DATE(YEAR(A1),MONTH(A1)+1,0))-A1)<=7,(DATE( YEAR(A1),MONTH(A1)+1,0)-WEEKDAY(DATE( YEAR(A1),MONTH(A1)+1,0))-A1)0) Also : =DAY(A1+15-WEEKDAY(A1-6))<8 Which is another way of saying the if NEXT saturday of A1 date is the last saturday of the month, the NEXT one (7 days later) will be in the first 7 days (<8) of next month. Regards, Daniel M. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Saturday of each month(check on Monday)
On Wed, 9 May 2007 21:57:48 -0400, "Daniel.M"
wrote: Also : =DAY(A1+15-WEEKDAY(A1-6))<8 Which is another way of saying the if NEXT saturday of A1 date is the last saturday of the month, the NEXT one (7 days later) will be in the first 7 days (<8) of next month. Regards, Daniel M. Very neat, Daniel! I have not seen you on the NG for quite a while. I've always liked your solutions to various date problems. --ron |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Saturday of each month(check on Monday)
=DAY(A1+15-WEEKDAY(A1-6))<8
Which is another way of saying the if NEXT saturday of A1 date is the last saturday of the month, the NEXT one (7 days later) will be in the first 7 days (<8) of next month. Thanks...it works but I don't fully understand how the formula get the result. Could you tell me more about it? Why +15 and WEEKDAY(A1-6)? |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Saturday of each month(check on Monday)
On Thu, 10 May 2007 17:44:56 +0800, "jimmy" wrote:
=DAY(A1+15-WEEKDAY(A1-6))<8 Which is another way of saying the if NEXT saturday of A1 date is the last saturday of the month, the NEXT one (7 days later) will be in the first 7 days (<8) of next month. Thanks...it works but I don't fully understand how the formula get the result. Could you tell me more about it? Why +15 and WEEKDAY(A1-6)? Let me see if I can explain it. But Daniel may do a better job: Basic principal: A1-WEEKDAY(A1) will compute the previous Saturday. A1+7-WEEKDAY(A1+7) will compute the current or next Saturday. Since there are seven days in the week, the above can be rewritten: A1+7-WEEKDAY(A1) Since we want, if A1=Saturday for the result to be the NEXT Saturday, then we start off by adding one to A1. A1+7+1-WEEKDAY(A1) But that, of course, will give us the following Sunday, not Saturday. So we also need to add a day to the date inside the WEEKDAY function: (Ignore the -6 for now). A1+7+1-WEEKDAY(A1+1) or A1+8-WEEKDAY(A1+1) That formula will give us the next Saturday. To test to see if the Saturday following A1 is the LAST Saturday of the month, we add another 7 days, and check to see what day of the month it is. If the next Saturday is the last Saturday of the month, then the Saturday following MUST be in the first SEVEN days of the following month: A1+8+7-WEEKDAY(A1+1) or DAY(A1+15-WEEKDAY(A1+1)) must be less than 8. So far as the "-6", it gives the same result as does "+1" since there are only seven days in a week. You could use a general formula, to compute the next weekday, of: =A1+8-WEEKDAY(A1+DOW) Where DOW, or day of week, is: 0 Sunday 1 Saturday 2 Friday 3 Thursday 4 Wednesday 5 Tuesday 6 Monday But that is a little harder to remember than the "normal" numbering of 0 Sunday 1 Monday 2 Tuesday 3 Wednesday 4 Thursday 5 Friday 6 Saturday so if you use the normal numbering, you can subtract DOW: -WEEKDAY(A1-DOW) --ron |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Saturday of each month(check on Monday)
Thanks Ron for explaining to Jimmy.
I _never_ saw his reply on my newsreader (OE). Guess it's time to change that one :-) As you rightly pointed out : understanding that A1+8-WEEKDAY(A1+1) , or WEEKDAY(A1-6) gives next Saturday is the key here. Regards, Daniel M. "Ron Rosenfeld" wrote in message ... On Thu, 10 May 2007 17:44:56 +0800, "jimmy" wrote: =DAY(A1+15-WEEKDAY(A1-6))<8 Which is another way of saying the if NEXT saturday of A1 date is the last saturday of the month, the NEXT one (7 days later) will be in the first 7 days (<8) of next month. Thanks...it works but I don't fully understand how the formula get the result. Could you tell me more about it? Why +15 and WEEKDAY(A1-6)? Let me see if I can explain it. But Daniel may do a better job: Basic principal: A1-WEEKDAY(A1) will compute the previous Saturday. A1+7-WEEKDAY(A1+7) will compute the current or next Saturday. Since there are seven days in the week, the above can be rewritten: A1+7-WEEKDAY(A1) Since we want, if A1=Saturday for the result to be the NEXT Saturday, then we start off by adding one to A1. A1+7+1-WEEKDAY(A1) But that, of course, will give us the following Sunday, not Saturday. So we also need to add a day to the date inside the WEEKDAY function: (Ignore the -6 for now). A1+7+1-WEEKDAY(A1+1) or A1+8-WEEKDAY(A1+1) That formula will give us the next Saturday. To test to see if the Saturday following A1 is the LAST Saturday of the month, we add another 7 days, and check to see what day of the month it is. If the next Saturday is the last Saturday of the month, then the Saturday following MUST be in the first SEVEN days of the following month: A1+8+7-WEEKDAY(A1+1) or DAY(A1+15-WEEKDAY(A1+1)) must be less than 8. So far as the "-6", it gives the same result as does "+1" since there are only seven days in a week. You could use a general formula, to compute the next weekday, of: =A1+8-WEEKDAY(A1+DOW) Where DOW, or day of week, is: 0 Sunday 1 Saturday 2 Friday 3 Thursday 4 Wednesday 5 Tuesday 6 Monday But that is a little harder to remember than the "normal" numbering of 0 Sunday 1 Monday 2 Tuesday 3 Wednesday 4 Thursday 5 Friday 6 Saturday so if you use the normal numbering, you can subtract DOW: -WEEKDAY(A1-DOW) --ron |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Saturday of each month(check on Monday)
On Thu, 10 May 2007 21:06:30 -0400, "Daniel.M"
wrote: Thanks Ron for explaining to Jimmy. I _never_ saw his reply on my newsreader (OE). Guess it's time to change that one :-) As you rightly pointed out : understanding that A1+8-WEEKDAY(A1+1) , or WEEKDAY(A1-6) gives next Saturday is the key here. Regards, Daniel M. I don't understand why that happens. I've missed occasional messages, too, using Forte's Agent as my newsreader. --ron |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last Saturday of each month(check on Monday)
Ron Rosenfeld wrote:
EOMONTH() is equivalent to DATE(YEAR(NOW()),MONTH(NOW())+1,1)-1 or, more simply: DATE(YEAR(NOW()),MONTH(NOW()),0) Shouldn't that read DATE(YEAR(NOW()),MONTH(NOW())+1,0) ? -- Gordon Rainsford London UK |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find 2nd & 4th saturday for every month | Excel Discussion (Misc queries) | |||
Second Saturday Of The Month | Excel Worksheet Functions | |||
Date calculation for Monday of one month to the Monday of the next | Excel Discussion (Misc queries) | |||
Help finding the date of the last Saturday of a given month | Excel Worksheet Functions | |||
first saturday in a month | Excel Worksheet Functions |