Date Range - Can this be done?

[gord9b]

We have a spreadsheet which is used as input for test data for testing mainframe programs. One of the items we do manually is calculate a date this way. Cell B4 has a "from date". Cell B5 has a "to date". Cell B6 has a day of the week, e.g. "Wednesday". What we have to do is come up with a date for a Wednesday between the "from date" and the "to date". Assuming the "from date" is "July 22/04" and the "to date" is "Aug. 21/04", how can we find a date for a Wednesday within that date range. I will even settle for the first Wednesday. We then have Mondays, etc. The output needs to be in the format of YYMMDD. Is this possible???????

 

[Aladin Akyurek]

On 2002-09-19 09:48, gord9b wrote:
We have a spreadsheet which is used as input for test data for testing mainframe programs. One of the items we do manually is calculate a date this way. Cell B4 has a "from date". Cell B5 has a "to date". Cell B6 has a day of the week, e.g. "Wednesday". What we have to do is come up with a date for a Wednesday between the "from date" and the "to date". Assuming the "from date" is "July 22/04" and the "to date" is "Aug. 21/04", how can we find a date for a Wednesday within that date range. I will even settle for the first Wednesday. We then have Mondays, etc. The output needs to be in the format of YYMMDD. Is this possible???????

I can't resist doing this using functions from morefunc.xll, an add-in downloadable from: http://longre.free.fr/english/index.html

Assuming that the from and to dates in B4 and B5 are true dates...

In B6 array-enter:

=INDEX(SETV(B4+INTVECTOR(B5-B4+1,0,1,1)),MATCH(4,WEEKDAY(GETV()),0))

will give you the first wednesday if available. Apply custom date format to B6 the way you want it.

To array-enter a formula, you need to hit control+shift+enter at the same time, not just enter.

 

[zzydhf]

start date = a2
end date = a3

First Wednesday:=IF((A2-WEEKDAY(A2,2))+10>=A3,"error",(A2-WEEKDAY(A2,2))+10)

A "middle" wednesday: =(DATE(YEAR(A2),MONTH(A2),DAY(A2)+(A3-A2)/2))-(WEEKDAY(DATE(YEAR(A2),MONTH(A2),DAY(A2)+(A3-A2)/2),2))+3

 

[gord9b]

I tried the first solution. Hitting the control+shift+enter gave the "{}" around the formula but, in the cell I have "#NAME?"
Here is a copy of my input.
=INDEX(setv(B4+intvector(B5-B4+1,0,1,1)),MATCH(4,WEEKDAY(getv()),0))
Inputs for cells B4 and B5 are "1-Sep-02" and "30-Sep-02". What did I do wrong?

 

[Aladin Akyurek]

On 2002-09-19 11:50, gord9b wrote:
I tried the first solution. Hitting the control+shift+enter gave the "{}" around the formula but, in the cell I have "#NAME?"
Here is a copy of my input.
=INDEX(setv(B4+intvector(B5-B4+1,0,1,1)),MATCH(4,WEEKDAY(getv()),0))
Inputs for cells B4 and B5 are "1-Sep-02" and "30-Sep-02". What did I do wrong?

You need to install the morefunc add-in first.

 

[gord9b]

The formula proposed by ZZYDHF works well except when the first date equals the date I am looking for. For instance, if the input date is Sep 4/02 which is a Wednesday, and I am looking for the first Wednesday, this formula returns Sep 11/02 instead of Sep 4/02. How can I get this formula to recognize the input date as the first date I am looking for if this is the case?

 

[zzydhf]

=IF(WEEKDAY(A2,2)=3,A2,IF((A2-WEEKDAY(A2,2))+10>=A3,"error",(A2-WEEKDAY(A2,2))+10))

 

[zzydhf]

=IF(WEEKDAY(A2,2)=3,A2,IF((A2-WEEKDAY(A2,2))+10>=A3,"error",(A2-WEEKDAY(A2,2))+10))