Using XL2000
I've searched the usual recommended sites for handling dates, but my needs
seem to be unique :(

I need a formula based on Today() that will produce:
1) First Monday of next month, IF there's one in the first week.
2) If no Monday in the first week, then first Thursday.
3) If neither, then Monday of second week.

--
David

In A1 put =TODAY()
In B1 put =DATE(YEAR(A1),MONTH(A1)+1,1) date of first day of next month
In C1 put =WEEKDAY(B1) the day of B1
In D1 put =CHOOSE(C1,B1+1,B1,B1+2,B1+1,B1,B1+3,B1+2) the projected day.

Please check D1 over all cases, I didn't fully test it.

Have a good day
--
Gary''s Student


"David" wrote:

Using XL2000
I've searched the usual recommended sites for handling dates, but my needs
seem to be unique :(

I need a formula based on Today() that will produce:
1) First Monday of next month, IF there's one in the first week.
2) If no Monday in the first week, then first Thursday.
3) If neither, then Monday of second week.

--
David

?B?R2FyeScncyBTdHVkZW50?= wrote

In A1 put =TODAY()
In B1 put =DATE(YEAR(A1),MONTH(A1)+1,1) date of first day of next month
In C1 put =WEEKDAY(B1) the day of B1
In D1 put =CHOOSE(C1,B1+1,B1,B1+2,B1+1,B1,B1+3,B1+2) the projected day.

Please check D1 over all cases, I didn't fully test it.

Have a good day

Thank you very much. I was hoping for a single cell solution, but I was
able to adjust cell references to put things outside my Print_Area and it
works as desired.

--
David

You are very welcome. You can combine the cells into a single cell solution
if this is what you need. I often do this after I get the piece-wise
solution to work.
--
Gary''s Student


"David" wrote:

?B?R2FyeScncyBTdHVkZW50?= wrote

In A1 put =TODAY()
In B1 put =DATE(YEAR(A1),MONTH(A1)+1,1) date of first day of next month
In C1 put =WEEKDAY(B1) the day of B1
In D1 put =CHOOSE(C1,B1+1,B1,B1+2,B1+1,B1,B1+3,B1+2) the projected day.

Please check D1 over all cases, I didn't fully test it.

Have a good day

Thank you very much. I was hoping for a single cell solution, but I was
able to adjust cell references to put things outside my Print_Area and it
works as desired.

--
David

?B?R2FyeScncyBTdHVkZW50?= wrote

You can combine the cells into a single cell solution
if this is what you need.

Given what we have in place, that would be one long formula, would it not?
And I wouldn't know how.

I use another formula to display the result, anyway:
="**Deliver "&TEXT(G89,"dddd, mmm d")&" Please**"
where G89 now contains the former D1 CHOOSE() formula

I will live with the 4-cell variety since it doesn't interfere with the
look and feel of my sheet. I've made the fontcolor of those cells white so
a viewer won't see them.

--
David

Hi David

As an alternative, you could use the following in a single cell
=EOMONTH(TODAY(),0)+1+(CHOOSE(WEEKDAY(EOMONTH(TODA Y(),0)+1),1,0,2,1,4,3,2))

For EOMOMTH to work you must have the Analysis Toolpak
ToolsAddins and check Analysis Toolpak

Regards

Roger Govier



David wrote:

?B?R2FyeScncyBTdHVkZW50?= wrote



You can combine the cells into a single cell solution
if this is what you need.



Given what we have in place, that would be one long formula, would it not?
And I wouldn't know how.

I use another formula to display the result, anyway:
="**Deliver "&TEXT(G89,"dddd, mmm d")&" Please**"
where G89 now contains the former D1 CHOOSE() formula

I will live with the 4-cell variety since it doesn't interfere with the
look and feel of my sheet. I've made the fontcolor of those cells white so
a viewer won't see them.

On Sun, 02 Oct 2005 05:00:57 -0700, David wrote:

Using XL2000
I've searched the usual recommended sites for handling dates, but my needs
seem to be unique :(

I need a formula based on Today() that will produce:
1) First Monday of next month, IF there's one in the first week.
2) If no Monday in the first week, then first Thursday.
3) If neither, then Monday of second week.

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

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?

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

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.

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

But maybe I'm misinterpreting something.

In any event, the first Monday after the first Sunday of the next month is
given by the formula below, where A1 is any date in a month.

=A1-DAY(A1)+41-DAY(A1-DAY(A1)+32)-
WEEKDAY((A1-DAY(A1)+39-DAY(A1-DAY(A1)+32)))



--ron

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.

--
David

Roger Govier wrote

As an alternative, you could use the following in a single cell
=EOMONTH(TODAY(),0)+1+(CHOOSE(WEEKDAY(EOMONTH(TODA Y(),0)+1),1,0,2,1,4,3
,2))

For EOMOMTH to work you must have the Analysis Toolpak
ToolsAddins and check Analysis Toolpak

Thanks, but I would prefer not to use the AP so I won't have to notify
users on other sites to whom I might send the file to turn it on.

--
David

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

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(YEAR(
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(YEAR (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

--
David

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(YEAR(
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(YEA R(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

Ron Rosenfeld wrote

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

Seen this method elsewhere - never used it. Good thought.

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.

Easier to follow for a novice (at least with date handling) like me.
Again, many thanks for all your time and effort on my behalf.

--
David

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

Ron Rosenfeld wrote

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.


Thought you might like to know I went the Named Range route and used only
'I rewrote part of it'. Found out today that Monday orders get delivered
right in the middle of the morning, totally disrupting everything, so I'm
switching to Thursday only deliveries (DOW = 5).

--
David

Thank you Roger.

Ideas came from others on this NG, including Daniel M.

Best,
--ron


On Mon, 03 Oct 2005 13:26:34 +0100, Roger Govier
wrote:

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(Y EAR(
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(Y EAR(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(YEA R(
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



--ron

On Mon, 03 Oct 2005 13:43:35 -0700, David wrote:

Thought you might like to know I went the Named Range route and used only
'I rewrote part of it'. Found out today that Monday orders get delivered
right in the middle of the morning, totally disrupting everything, so I'm
switching to Thursday only deliveries (DOW = 5).

Thanks for the feedback. Certainly makes for a simpler solution.


--ron