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