Filtering by Dates

[Pibsandsquibs]

Hello, I have a spreadsheet that has multiple shipments recorded either with a past date or dates in the future (up to 2 months).

Is there a way that I can get excel to filter everything automatically that is arriving in the next 21 days as an example and then it automatically updates this on a daily basis

So based on todays date being 29/08/24 everything that has an arrival date between now and the 19/09/24 appears and then tomorrow shipments that have an arrival date between 30/08/24 and 20/09/24 appears adding the 20/09/24 to the list. Thanks

 

[RoryA]

Yes, you can use the FILTER function for that since you have 365. Basic syntax:

=FILTER(data,(datecol>=TODAY())*(datecol<=TODAY()+21))

where data is the whole data range you want returned and datecol is just the date column.

 

[Phuoc]

Try this:

Book1
	A	B	C	D	E
1
2	A	28/8/2024		B	29/8/2024
3	B	29/8/2024		C	30/8/2024
4	C	30/8/2024		D	31/8/2024
5	D	31/8/2024
6	E	1/10/2024
7
Sheet1

Cell Formulas
Range	Formula
D2:E4	D2	=LET(a,TODAY(),b,a+21,FILTER(A2:B6,(B2:B6>=a)*(B2:B6<=b),""))
Dynamic array formulas.

 

[Pibsandsquibs]

Yes, you can use the FILTER function for that since you have 365. Basic syntax:

=FILTER(data,(datecol>=TODAY())*(datecol<=TODAY()+21))

where data is the whole data range you want returned and datecol is just the date column.

Hi @RoryA many thanks for this, it is amazing, one more question is there a way i can get it to sort the datecol in date order automatically?

 

[Pibsandsquibs]

Try this:

Book1
	A	B	C	D	E
1
2	A	28/8/2024		B	29/8/2024
3	B	29/8/2024		C	30/8/2024
4	C	30/8/2024		D	31/8/2024
5	D	31/8/2024
6	E	1/10/2024
7
Sheet1

Cell Formulas
Range	Formula
D2:E4	D2	=LET(a,TODAY(),b,a+21,FILTER(A2:B6,(B2:B6>=a)*(B2:B6<=b),""))
Dynamic array formulas.

thank you @Phuoc this also works, is there a way with this to get the date column to sort in date order automatically?

 

[RoryA]

Yes, you can wrap that in a SORT function and specify the column number:

=SORT(FILTER(data,(datecol>=TODAY())*(datecol<=TODAY()+21)),datecol_number)

 

[Pibsandsquibs]

Yes, you can wrap that in a SORT function and specify the column number:

=SORT(FILTER(data,(datecol>=TODAY())*(datecol<=TODAY()+21)),datecol_number)

Thanks @RoryA I might be missing something. For the datecol_number that you have added at the end of the formula. Is that the original datecol (as in where it is pulling the data from in the first formula) or where it is in the filter.

My results are not filtering in numerical order at the moment.

 

[RoryA]

Are you including the date column in the output of the filter? If so, it's the position of the date column in the results. If not, we'll need to redo the formula to sort the data first, then filter.

 

[Pibsandsquibs]

Are you including the date column in the output of the filter? If so, it's the position of the date column in the results. If not, we'll need to redo the formula to sort the data first, then filter.

@RoryA yes the date column is included. I have just entered 24 as this is the date column is the 24th column and excel didn't like it and wanted to multiply the formula by 24 which there returned errors

 

[Pibsandsquibs]

@RoryA yes the date column is included. I have just entered 24 as this is the date column is the 24th column and excel didn't like it and wanted to multiply the formula by 24 which there returned errors

@RoryA it was me, apologies I wasn't putting the , in front of the 24. Thank you