Excellent solution Ron!!

Regards

Roger Govier



Ron Rosenfeld wrote:

On Sun, 02 Oct 2005 15:48:31 -0700, David wrote:



Ron Rosenfeld wrote



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


True, but my brain failed to envision it that way.



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


Yep, that'll do it, but what a formula to get this:

** Deliver on Thursday, Nov 3 Please **

="** Deliver on " & TEXT(MIN(DATE(YEAR(TODAY()),MONTH(TODAY())+1,8)-WEEKDAY
(DATE(YEAR(TODAY()),MONTH(TODAY())+1,6)),DATE(YE AR(TODAY()),MONTH(TODAY())+
1,8)-WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,3)))," dddd, mmm d") & "
Please **"

And harder to test or adjust/adapt than the "Gary's Student" offering.

Well, at least I got my single-cell solution <g
Many thanks



Not sure how you might want to adjust it, but you could NAME the formula and
then use the Name in your cell.

Insert/Name/Define
Names in Workbook: DelivDate
Refers to:
=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)))

Then, in your cell, merely type:

="** Deliver on "&TEXT(DelivDate,"dddd, mmm d")&" Please **"

-----------------------------

So far as modifying it, I guess it depends on how you want to modify it.
Perhaps it will help if I rewrote part of it:

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,8)-WEEKDAY(
DATE(YEAR(TODAY()),MONTH(TODAY())+1,8-DOW))

For DOW, substitute the Day of the Week that you want to find the first date of
in the next month. Sunday = 1; Monday = 2; etc.

So if your delivery dates were going to be Tues or Fri, with the same rules as
above, then:

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


--ron