On Sun, 02 Oct 2005 12:22:28 -0700, David wrote:

Ron Rosenfeld wrote

Answers in line,

What, exactly, do you mean by the "first week" and "second week".

Picture a calendar grid 5 rows by 7 columns (Sun-Sat)
First week would be in Row 1, second in Row 2

And in condition two, do you mean the first Thursday only if there's a
Thursday in the first week? Otherwise, what does the "if neither" in
item 3 mean?

No. Month could begin on Fri or Sat. That triggers 'if neither'
condition, no Monday or Thursday in the first week.

And is the first week the week starting with the first Sunday of the
month?

Month's dates could start anywhere in first week.

If so, you could just look for the first Monday after the first
Sunday. Because if your definitions are as above, I don't see any way
that the first Thursday of the month could be in the first week, if
the first Monday was not.

Not sure I follow, but first Thursday in my scenario means if there is
not a Monday in Row 1.

And, of course, there always has to be a first Thursday.

But not necessarily in Row 1

But maybe I'm misinterpreting something.

Our organization receives deliveries only on Monday or Thursday. I want
to predict which would occur first next month so I can stock needed items
through that month's first available delivery date and automatically
indicate that date on the order form.

It sounds like another way of expressing this would be the earlier of the first
Monday or the first Thursday of the next month. So:

=MIN(A8-DAY(A8)+40-DAY(A8-DAY(A8)+32)-WEEKDAY(A8-DAY(A8)+38-DAY(A8-DAY(A8)+32)),
A8-DAY(A8)+40-DAY(A8-DAY(A8)+32)-WEEKDAY(A8-DAY(A8)+35-DAY(A8-DAY(A8)+32)))

or, using TODAY in place of a date in A8:

=MIN(TODAY()-DAY(TODAY())+40-DAY(TODAY()-DAY(TODAY())+32)-
WEEKDAY(TODAY()-DAY(TODAY())+38-DAY(TODAY()-DAY(TODAY())+32)),
TODAY()-DAY(TODAY())+40-DAY(TODAY()-DAY(TODAY())+32)-
WEEKDAY(TODAY()-DAY(TODAY())+35-DAY(TODAY()-DAY(TODAY())+32)))

Slightly shorter, but requiring more different functions:

=MIN(DATE(YEAR(A8),MONTH(A8)+1,8)-WEEKDAY(DATE(YEAR(A8),MONTH(A8)+1,6)),
DATE(YEAR(A8),MONTH(A8)+1,8)-WEEKDAY(DATE(YEAR(A8),MONTH(A8)+1,3)))

or, using TODAY() in place of A8 as befo

=MIN(DATE(YEAR(TODAY()),MONTH(TODAY())+1,8)-WEEKDAY(
DATE(YEAR(TODAY()),MONTH(TODAY())+1,6)),DATE(YEAR(
TODAY()),MONTH(TODAY())+1,8)-WEEKDAY(DATE(YEAR(
TODAY()),MONTH(TODAY())+1,3)))


--ron