# Date Range - Can this be done?

#### gord9b

##### Board Regular
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???????

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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???????

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.

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

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?

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.

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?

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

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

Replies
10
Views
381
Replies
23
Views
498
Replies
3
Views
137
Replies
0
Views
116
Replies
4
Views
247

1,203,468
Messages
6,055,595
Members
444,800
Latest member
KarenTheManager

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back