Last Saturday of each month(check on Monday)

[jimmy]

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

 

[Guest]

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]

Thanks Joel, I also can't use EOMONTH(), but your formula fit my need. ^^

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[Guest]

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.

 

[jimmy]

Thanks Ron, but it got mistake on June 2007. ^^

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[jimmy]

Yup, the previous one didn't work if the month end is SAT..

 

[Daniel.M]

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.

 

[Ron Rosenfeld]

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

 

[jimmy]

=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)?

 

[Ron Rosenfeld]

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

 

[Daniel.M]

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]

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

 

[Gordon Rainsford]

or, more simply:

DATE(YEAR(NOW()),MONTH(NOW()),0)

Shouldn't that read

DATE(YEAR(NOW()),MONTH(NOW())+1,0) ?