Hi all,

I've written the following formula to check if it is the first Monday after
every 29th. It seems work but I don't know it is correct or not, please give
advice, many thanks.

=AND(DAY(G3+7-WEEKDAY(G3-2)-6)=23,DAY(G3+7-WEEKDAY(G3-2)-6)<=29)

I've written the following formula to check if it is the first Monday
after every 29th. It seems work but I don't know it is correct or not,
please give advice, many thanks.

=AND(DAY(G3+7-WEEKDAY(G3-2)-6)=23,DAY(G3+7-WEEKDAY(G3-2)-6)<=29)

Just out of curiosity, is what do you do for February of a non-leap year? Is
February 28th or March 1st treated as the 29th of the month?

Rick

On Mon, 21 May 2007 09:01:23 +0800, "jimmy" wrote:

Hi all,

I've written the following formula to check if it is the first Monday after
every 29th. It seems work but I don't know it is correct or not, please give
advice, many thanks.

=AND(DAY(G3+7-WEEKDAY(G3-2)-6)=23,DAY(G3+7-WEEKDAY(G3-2)-6)<=29)


I don't understand what you are doing.

"... check if it is the first Monday after every 29th..."

What is *it* ?

If *it* is the date in G3, then Wednesday, 28 Feb 2007 returns TRUE, even
though it is not a Monday, nor is it after the 29th.
--ron

Sorry, *it* is refer to the date store in G3. First of all, ignore the leap
year. I just concern on MONDAY only since the vba code only check on the
cell that contains this formula on Monday. I would like to get the result
of, if the date in G3 is the first Monday on or after the 29th of each
month, return True, other Mondays return false.

"Ron Rosenfeld"
...
On Mon, 21 May 2007 09:01:23 +0800, "jimmy" wrote:

Hi all,

I've written the following formula to check if it is the first Monday
after
every 29th. It seems work but I don't know it is correct or not, please
give
advice, many thanks.

=AND(DAY(G3+7-WEEKDAY(G3-2)-6)=23,DAY(G3+7-WEEKDAY(G3-2)-6)<=29)


I don't understand what you are doing.

"... check if it is the first Monday after every 29th..."

What is *it* ?

If *it* is the date in G3, then Wednesday, 28 Feb 2007 returns TRUE, even
though it is not a Monday, nor is it after the 29th.
--ron

Sorry, *it* is refer to the date store in G3. First of all, ignore the
leap year. I just concern on MONDAY only since the vba code only check on
the cell that contains this formula on Monday. I would like to get the
result of, if the date in G3 is the first Monday on or after the 29th of
each month, return True, other Mondays return false.

Assuming you mean that March 1st would qualify as the first day after the
imaginary February 29th of a non-leap year, then the following formula will
return True if the date in G3 falls is a first Monday following the 29th of
a month...

=(G3=DATE(YEAR(G3),MONTH(G3)-(DAY(G3)<7),28)+8-WEEKDAY(DATE(YEAR(G3),MONTH(G3)-(DAY(G3)<7),28)-1))

Maybe that statement above needs a little clarification (to make sure I
actually calculated what you wanted). If the date in G3 falls within the
first week of its month, then the date it is compared to is the first Monday
following the 29th of the previous month. Take June 4, 2007 as an example...
the above formula returns True for if G3 contains the date June 4, 2007
because that date is the first Monday that follows May 29, 2007. Take
September 29, 2008 as another example. If G3 contains this date, the above
formula also returns True because that date falls on a Monday.

Rick

Sorry, *it* is refer to the date store in G3. First of all, ignore the
leap year. I just concern on MONDAY only since the vba code only check on
the cell that contains this formula on Monday. I would like to get the
result of, if the date in G3 is the first Monday on or after the 29th of
each month, return True, other Mondays return false.

Assuming you mean that March 1st would qualify as the first day after the
imaginary February 29th of a non-leap year, then the following formula
will return True if the date in G3 falls is a first Monday following the
29th of a month...

=(G3=DATE(YEAR(G3),MONTH(G3)-(DAY(G3)<7),28)+8-WEEKDAY(DATE(YEAR(G3),MONTH(G3)-(DAY(G3)<7),28)-1))

Maybe that statement above needs a little clarification (to make sure I
actually calculated what you wanted). If the date in G3 falls within the
first week of its month, then the date it is compared to is the first
Monday following the 29th of the previous month. Take June 4, 2007 as an
example... the above formula returns True for if G3 contains the date June
4, 2007 because that date is the first Monday that follows May 29, 2007.
Take September 29, 2008 as another example. If G3 contains this date, the
above formula also returns True because that date falls on a Monday.

While this is not an answer to the question you asked, I thought you might
be interested in the complimentary functional code... given a date in G3,
what is the date of the first Monday on or after the 29th of G3's month? The
answer is...

=36+G3-DAY(G3)-WEEKDAY(G3-DAY(G3)-1)

You can't use this function directly because it uses G3's month to calculate
from. For example, if you set June 4, 2007 into G3, it won't return June 4,
2007 even though that date is a first Monday on or after a 29th of some
month because it doesn't know to look at the previous month. As I said, I
just though you might find the simplicity of the function interesting.

Rick

A small, but important addition to my comments...

While this is not an answer to the question you asked, I thought you might
be interested in the complimentary functional code... given a date in G3,
what is the date of the first Monday on or after the 29th of G3's month?
The answer is...

=36+G3-DAY(G3)-WEEKDAY(G3-DAY(G3)-1)

You can't use this function directly

to solve your original problem

because it uses G3's month to calculate from. For example, if you set
June 4, 2007 into G3, it won't return June 4, 2007 even though that
date is a first Monday on or after a 29th of some month because it
doesn't know to look at the previous month. As I said, I just

thought

you might find the simplicity of the function interesting.

Rick

Rick,
Maybe all these years of one-liners in VB you have missed your true calling
of Excel worksheet formulae. <g

NickHK

"Rick Rothstein (MVP - VB)" wrote in
message ...
Sorry, *it* is refer to the date store in G3. First of all, ignore the
leap year. I just concern on MONDAY only since the vba code only check
on
the cell that contains this formula on Monday. I would like to get the
result of, if the date in G3 is the first Monday on or after the 29th
of
each month, return True, other Mondays return false.

Assuming you mean that March 1st would qualify as the first day after
the
imaginary February 29th of a non-leap year, then the following formula
will return True if the date in G3 falls is a first Monday following the
29th of a month...


=(G3=DATE(YEAR(G3),MONTH(G3)-(DAY(G3)<7),28)+8-WEEKDAY(DATE(YEAR(G3),MONTH(G
3)-(DAY(G3)<7),28)-1))

Maybe that statement above needs a little clarification (to make sure I
actually calculated what you wanted). If the date in G3 falls within the
first week of its month, then the date it is compared to is the first
Monday following the 29th of the previous month. Take June 4, 2007 as an
example... the above formula returns True for if G3 contains the date
June
4, 2007 because that date is the first Monday that follows May 29, 2007.
Take September 29, 2008 as another example. If G3 contains this date,
the
above formula also returns True because that date falls on a Monday.

While this is not an answer to the question you asked, I thought you might
be interested in the complimentary functional code... given a date in G3,
what is the date of the first Monday on or after the 29th of G3's month?
The
answer is...

=36+G3-DAY(G3)-WEEKDAY(G3-DAY(G3)-1)

You can't use this function directly because it uses G3's month to
calculate
from. For example, if you set June 4, 2007 into G3, it won't return June
4,
2007 even though that date is a first Monday on or after a 29th of some
month because it doesn't know to look at the previous month. As I said, I
just though you might find the simplicity of the function interesting.

Rick

Maybe all these years of one-liners in VB you have missed your true
calling
of Excel worksheet formulae. <g

LOL... Yes, Excel worksheet formulas DO seem to cater to that, don't they?

By the way, I don't think the majority of those in this newsgroup will
understand our exchange here. Besides, I'd have to guess the real kings of
one-liners probably reside here in the Excel newsgroups.

Rick

Rick,
Well, if you hang around here for a while, I'm sure others will appreciate
your contributions also.

As for some of the best (worst ?), here's a few:
http://www.dailydoseofexcel.com/arch...ugly-formulas/

NickHK

"Rick Rothstein (MVP - VB)" wrote in
message ...
Maybe all these years of one-liners in VB you have missed your true
calling
of Excel worksheet formulae. <g

LOL... Yes, Excel worksheet formulas DO seem to cater to that, don't they?

By the way, I don't think the majority of those in this newsgroup will
understand our exchange here. Besides, I'd have to guess the real kings of
one-liners probably reside here in the Excel newsgroups.

Rick

Well, if you hang around here for a while, I'm sure others will appreciate
your contributions also.

The big problem is I am relatively new to Excel (yeah, I plotzed around in
the past, but no real serious spreadsheet designs), so I am liable to
develop some long-winded formula only to find there is a direct, built-in
formula to do the same thing.

As for some of the best (worst ?), here's a few:
http://www.dailydoseofexcel.com/arch...ugly-formulas/

Ugly formulas? Why, those are things of beauty to my eyes... I think I am
going to like it here.<g

Rick

=36+G3-DAY(G3)-WEEKDAY(G3-DAY(G3)-1)

You can't use this function directly because it uses G3's month to
calculate from. For example, if you set June 4, 2007 into G3, it won't
return June 4, 2007 even though that date is a first Monday on or after a
29th of some month because it doesn't know to look at the previous month.
As I said, I just though you might find the simplicity of the function
interesting.

I lied... you can use the above formula... well, a modification of it at
least... to do what initially asked. If the date in G3 is a first Monday on
or after the 29th of a month, then the following will return True...

=(36-7*(DAY(G3)<7)=DAY(G3-7*(DAY(G3)<7))+WEEKDAY(G3-7*(DAY(G3)<7)-DAY(G3-7*(DAY(G3)<7))-1))

I know it looks ugly, but all I did in the above formula is to subtract 7
days from **all** occurrences of G3's date value (and the constant 36 value)
if the day number of G3 is less than 7 in order to do the comparison in the
previous month).

Rick

On Mon, 21 May 2007 10:56:38 +0800, "jimmy" wrote:

Sorry, *it* is refer to the date store in G3. First of all, ignore the leap
year. I just concern on MONDAY only since the vba code only check on the
cell that contains this formula on Monday. I would like to get the result
of, if the date in G3 is the first Monday on or after the 29th of each
month, return True, other Mondays return false.


I think this will do what you want, if I understand you correctly:

=AND(WEEKDAY(G3)=2,DAY(G3-7)=22,DAY(G3-7)<29)


--ron

On Mon, 21 May 2007 00:45:41 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

Sorry, *it* is refer to the date store in G3. First of all, ignore the
leap year. I just concern on MONDAY only since the vba code only check on
the cell that contains this formula on Monday. I would like to get the
result of, if the date in G3 is the first Monday on or after the 29th of
each month, return True, other Mondays return false.

Assuming you mean that March 1st would qualify as the first day after the
imaginary February 29th of a non-leap year, then the following formula will
return True if the date in G3 falls is a first Monday following the 29th of
a month...

=(G3=DATE(YEAR(G3),MONTH(G3)-(DAY(G3)<7),28)+8-WEEKDAY(DATE(YEAR(G3),MONTH(G3)-(DAY(G3)<7),28)-1))

Maybe that statement above needs a little clarification (to make sure I
actually calculated what you wanted). If the date in G3 falls within the
first week of its month, then the date it is compared to is the first Monday
following the 29th of the previous month. Take June 4, 2007 as an example...
the above formula returns True for if G3 contains the date June 4, 2007
because that date is the first Monday that follows May 29, 2007. Take
September 29, 2008 as another example. If G3 contains this date, the above
formula also returns True because that date falls on a Monday.

Rick

Using your formula:

FALSE Monday, February 28, 2011
FALSE Monday, March 07, 2011

He goes 8 weeks between "TRUE"'s.


--ron

Using your formula:

FALSE Monday, February 28, 2011
FALSE Monday, March 07, 2011

He goes 8 weeks between "TRUE"'s.

Using <7 was a bad assumption on my part (I forgot that February 28th pushes
a full 7 days); however, using <8 should work though...

=(G3=DATE(YEAR(G3),MONTH(G3)-(DAY(G3)<8),28)+8-WEEKDAY(DATE(YEAR(G3),MONTH(G3)-(DAY(G3)<8),28)-1))

Rick

I lied... you can use the above formula... well, a modification of it at
least... to do what initially asked. If the date in G3 is a first Monday
on or after the 29th of a month, then the following will return True...

=(36-7*(DAY(G3)<7)=DAY(G3-7*(DAY(G3)<7))+WEEKDAY(G3-7*(DAY(G3)<7)-DAY(G3-7*(DAY(G3)<7))-1))

I know it looks ugly, but all I did in the above formula is to subtract 7
days from **all** occurrences of G3's date value (and the constant 36
value) if the day number of G3 is less than 7 in order to do the
comparison in the previous month).

A minor change is needed to the above formula... the <7's have to be changed
to <8's (see Ron's reply to me and mine to him)...

=(36-7*(DAY(G3)<8)=DAY(G3-7*(DAY(G3)<8))+WEEKDAY(G3-7*(DAY(G3)<8)-DAY(G3-7*(DAY(G3)<8))-1))

Rick

On Mon, 21 May 2007 14:15:51 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

=(G3=DATE(YEAR(G3),MONTH(G3)-(DAY(G3)<8),28)+8-WEEKDAY(DATE(YEAR(G3),MONTH(G3)-(DAY(G3)<8),28)-1))

That works, although I do prefer mine posted earlier today, since it is shorter
and, I think has a simpler algorithm.

I did "translate" the OP's request from:

"The First Monday on or after the 29th"

to

"The First Monday after the 28th"

That made February easier to deal with conceptually, for me.

=AND(WEEKDAY(G3)=2,DAY(G3-7)=22,DAY(G3-7)<29)


--ron