Future date formula

D

David

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.
 
G

Gary''s Student

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
 
D

David

=?Utf-8?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
Click to expand...

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.
 
G

Gary''s Student

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.
 
D

David

=?Utf-8?B?R2FyeScncyBTdHVkZW50?= wrote
You can combine the cells into a single cell solution
if this is what you need.
Click to expand...

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.
 
R

Roger Govier

Hi David

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

For EOMOMTH to work you must have the Analysis Toolpak
Tools>Addins> and check Analysis Toolpak

Regards

Roger Govier
 
R

Ron Rosenfeld

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.
Click to expand...

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
 
D

David

Ron Rosenfeld wrote

Answers in line,
What, exactly, do you mean by the "first week" and "second week".
Click to expand...

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?
Click to expand...

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?
Click to expand...

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.
Click to expand...

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.
Click to expand...

But not necessarily in Row 1
But maybe I'm misinterpreting something.
Click to expand...

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.
 
D

David

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

For EOMOMTH to work you must have the Analysis Toolpak
Tools>Addins> and check Analysis Toolpak
Click to expand...

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.
 
R

Ron Rosenfeld

Ron Rosenfeld wrote

Answers in line,


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


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


Month's dates could start anywhere in first week.


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


But not necessarily in Row 1


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.
Click to expand...

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 before:

=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
 
D

David

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.
Click to expand...

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)))
Click to expand...

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
 
R

Ron Rosenfeld

Ron Rosenfeld wrote


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


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
Click to expand...

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
 
D

David

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)))
Click to expand...

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.
Click to expand...

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.
 
D

David

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.
Click to expand...

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

Ron Rosenfeld

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).
Click to expand...

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


--ron
 
Top