How to select a perioid between two dates?

[Waimea]

Hi,

in column G I have a range of dates, mostly without breaks.

Date

2021-09-15​

2021-09-15​

2021-09-14​

2021-09-14​

2021-09-14​

2021-09-13​

2021-09-13​

2021-09-13​

2021-09-12​

2021-09-12​

2021-09-12​

How can I easily select a period of dates between to dates? I am trying to use one cell with "From date" and one cell with "To date",

Ex. If I wanted to select all dates between 2021-09-15 and 2021-09-13, and if left blank I would get all dates. And I will get all data in the other columns where the dates match.

From date: 2021-09-13

To date: 2021-09-15

All suggestions are welcome. Perhaps there is a better way using comboboxes or dropdowns for year, month and day? Or slicers?

EDIT:

I spelled "period" wrong in the thread title, perhaps someone could correct it?

 

[jasonb75]

Select as in filter? As in create an array for a formula? Select to copy? Something else?

 

[Waimea]

Hi jasonb75,

thank you for your reply.

I want to create a new sheet/table with the data that is between the two selected dates. If omitted, I would get all data.

In a later step I want to do some calculations on the data that is between the two date ranges.

In my data I can have more then one date range. So you could say that the date ranges are repeated but with different data in the other columns.

Ex.

2019-09-20​

2019-09-19​

2019-09-19​

2019-09-18​

2019-09-17​

2019-09-13​

2019-09-13​

2019-09-13​

2019-09-13​

2019-09-13​

2019-09-12​

2019-09-12​

2019-09-12​

2019-09-12​

2021-09-15​

2021-09-14​

2021-09-14​

2021-09-15​

Does this make sense? In my date column the date ranges can exist multiple times, so I could have data from 2019-09-10 to 2019-09-25 and then data is 2019-09-10 to 2019-09-25 again but with different other data. I want to get the data in all the date ranges that are valid.

 

[jasonb75]

Maybe you could filter the original by date using the reference dates as start and end points, then copy and paste to a new sheet?

 

[Tupe77]

Column G type is date?
How many possible rows?
What columns is copied?
What do You mean by: and if left blank I would get all dates

 

[jasonb75]

You could do something like this with formulas, but it appears that your data is not in any order so you might encounter problems with anything other than filtering. (column B in the example contains random values for testing).

Book1
	A	B	C	D	E	F
1	2019-09-20	0.3324142		From	Results
2	2019-09-19	0.57491		2019-09-16	2019-09-17	0.7646837
3	2019-09-19	0.4750512		To	2019-09-13	0.2871864
4	2019-09-18	0.6133957		2021-09-14	2019-09-13	0.2856923
5	2019-09-17	0.7646837			2019-09-13	0.3521906
6	2019-09-13	0.2871864			2019-09-13	0.5912346
7	2019-09-13	0.2856923			2019-09-13	0.7903674
8	2019-09-13	0.3521906			2019-09-12	0.7465116
9	2019-09-13	0.5912346			2019-09-12	0.9693586
10	2019-09-13	0.7903674			2019-09-12	0.2849126
11	2019-09-12	0.7465116			2019-09-12	0.1309111
12	2019-09-12	0.9693586			2021-09-15	0.0083453
13	2019-09-12	0.2849126			2021-09-14	0.0070639
14	2019-09-12	0.1309111			2021-09-14	0.8008066
15	2021-09-15	0.0083453
16	2021-09-14	0.0070639
17	2021-09-14	0.8008066
18	2021-09-15	0.6161982
19
Sheet4

Cell Formulas
Range	Formula
E2:F14	E2	=INDEX(A:A,IF(D2="",1,XMATCH(D2,A:A,1,1))):INDEX(B:B,IF(D4="",XMATCH(1E+100,A:A,-1,2),XMATCH(D4,A:A,1,-1)))
Dynamic array formulas.

 

[Waimea]

Column G type is date?
How many possible rows?
What columns is copied?
What do You mean by: and if left blank I would get all dates

Hi Tupe77,

thank you for your reply!

I have 10 000 x 80 rows at the most.

Say I have data from


2019-08-01

to

2019-09-01.

I would like to select e.x. dates 2019-08-01 to 2019-08-08 and only get data within this range. But if I leave the from and to blank, I would get data from 2019-08-01 to 2019-09-01.

 

[Waimea]

Hi jasonb75,

thank you for your reply.

When you say filtering, do you mean filter and/or advanced filter?

Perhaps I could create a sub that checks if a date is within the selected date period?

Perhaps create an array with the date range in and then load the data from the date periods in another array and check item per item.

 

[Waimea]

I found this snipppet here Get Array of Dates Between 2 Dates

Dim dtDate as Date, dtStartDate as date, dtEndDate as Date

dtStartDate = #8/1/2019#
dtEndDate = #8/31/2019#

For dtDate = dtStartDate To dtEndDate
    'code to do each date
Next dtDate

Perhaps this could be a part of my code?

 

[jasonb75]

When you say filtering, do you mean filter and/or advanced filter?

Regular filter. Click the filter arrow at the top of the column, then go to Date Filters > Between and choose your dates. Copy and paste your data to a new sheet, all while recording a macro so that you have the base code to work from.