Automatically filter a report with date and time, with other criteria

Printable View

We have to run a weekly report with over 8000 lines, and there are many types of filter, it takes a while to filter different things.

Let's say I want to filter a report for the week of june 3rd.

So on Monday, i need to filter by date from may 22 8:00am to may 23, 7:59 am, tuesday will be like may 23 8am to to may 24 7:59am, wednesday will be something like may 24 8:00am to may 27 7:59am, because of the weekend...

I also need to use multiple filters, not just date and time. So one could be may 22 8:00 am to may 23 7:59 am, along with another filter for rush and non-rush as an exemple.

Date and time rush Banana Peter

5/24/2024 9:52

non-rush Apple Susan

5/28/2024 12:18

rush Peach Susan

5/30/2024 5:02

rush Banana Susan

5/23/2024 12:17

non-rush Apple Clark

5/28/2024 12:18

non-rush Apple Joey

So I need to do something like adding a filter between may 23rd 12:00pm to may 24 11:59am, then select rush, Banana and Peter. So I will have only one line (so the result is 1).

How would I do that automatically without using filters ? Maybe I can add a baseline date in one of the empty cells next to the report like June 3rd, then every Monday will be baseline date - 11 days. I can also add time in an empty cell like 8:00 am and 7:59 am. However, as far as the formula goes, I am stuck there. Also, the date and date (both date and time are in the same cell) column paste as a custom format.

I wonder what is the fastest and most efficient way to do it ?

06-05-2024, 03:28 AM
p45cal

Sounds a bit convoluted - could you rig up a realistic workbook with this sort of thing in it and attach it here? Saves us guessing (wrongly).
06-05-2024, 01:59 PM
MasterBash

1 Attachment(s)

Here it is. I had to remove multiple columns (usually we have around 12), because it contained private information. I also removed over 6000 lines. This is a realistic workbook. Sheet 2 offers a bit of an explanation with the date range I must use.
06-07-2024, 10:03 AM
p45cal

2 Attachment(s)

Some ideas for you in the attached, not a full solution.
Cell P8 of the Sheet1 sheet contains your count.
You'll see in that cell that there are references to cells in the range I6:M6. Most of these are manually entered by you; start date, time of day, Service level and Shipment, highlighted in green. The one cell that is used in this formula is cell J6. This works out the end date 1 business working day added to the start date, so weekends are taken into account.
You have mentioned the likes of "to filter by date from may 22 8:00am to may 23, 7:59 am" where instead of using the 7:59 bit I stuck to using once instant in time (8am) but used >=8am on one side and <8am on the other so that times such as 7:59:30secs are not missed out altogether.
Sometimes, such a formula is difficult to debug/check so to see what's included in the count, there's a formula in cell I8 which aims to show which rows of the source table are included in the count. This formula isn't needed, it's just there to picture/verify that the count is correct (or not). It uses the same parameters as cell P8.

Turning to sheet Sheet2 in the top left corner is an example of how you might want to use the above.
Here, the green cells are your manual input; only one date in cell A2, and values in rows 5 to 7. All the rest are calculated. Row 8 contains the counts.

Attachment 31624
06-07-2024, 02:01 PM
MasterBash

wow, this is actually really cool and it is working great ! :)
If I actually wanted to select 2 filters in the same column... or just remove a single one. Let's say I wanted to select UPS and Fedex. Or select themm all and just remove Canada post. How would I do that ?

=COUNTIFS(Table1[[Date Added]:[Date Added]];">=" & A3+A5;Table1[[Date Added]:[Date Added]];"<" & A4+A5;Table1[[Service Level]:[Service Level]];A6;Table1[[Shipment]:[Shipment]];A7)
I tried adding more cells at the end Table1[[Service Level]:[Service Level]];A6+A7+A8, etc... and add rows accordingly. However, that does not seem to work. Result is 0.

I also tried more than one per line (Let's say UPS and DHL on the same line), but the result is 0.

Thank you so much!
06-07-2024, 03:34 PM
p45cal

Quote:

Originally Posted by MasterBash

wow, this is actually really cool and it is working great ! :)
If I actually wanted to select 2 filters in the same column... or just remove a single one. Let's say I wanted to select UPS and Fedex. Or select themm all and just remove Canada post. How would I do that ?

It's hard work. I'll have a think about it, but it may not happen!
I can do one where you can delete the service level to a blank cell in row 6 so that Service Level isn't filtered at all. Also the same with Shipment; just press the delete key on cells in rows 6 and/or 7 to remove that filter.
For this to work like that, change the formula in cell A8 to:

Code:

=COUNTIFS(Table1[[Date Added]:[Date Added]],">=" & A3+A5,Table1[[Date Added]:[Date Added]],"<" & A4+A5,Table1[[Service Level]:[Service Level]],IF(A6="","*",A6),Table1[[Shipment]:[Shipment]],IF(A7="","*",A7))

and copy across.
06-10-2024, 07:46 AM
p45cal

Will the five counts (different dates) share the same filtering (saves having to adjust 5 sets of filters)?
06-10-2024, 02:32 PM
MasterBash

Yes, but there are 3 different sets of filters for Date Time/Service Level/Shipment, but you made it very easy to simply copy and paste the table on sheet2 and change the data with different date/time. There will be 2 more tables on sheet2.

This is what we do with the filters :
One being a filter with only UPS + Filter without ground, 8:00am to 7:59am.
The other being a filter with only DHL + Filter without ground, 12:00am to 11:59am, that will be the second table.
Then there is a Filter with DHL and UPS + Filter with ground only, 12:00am to 11:59am, this will be the third table.

There will be a total of 3 tables on sheet2. One for each filters with different date/time and I will adjust the dates and time accordingly.
06-10-2024, 04:34 PM
p45cal

2 Attachment(s)

Look at cells A5:E7 of the Sheet2 sheet in the attached.
It assumes you have static sets of values for the Service Level and Shipment columns, because the hard-coded values in the formula (circled in the pic below) represent what's included in the count. You can add/remove hard-coded values in those parts of the formula. They must be comma separated, regardless of which locale you're in.
There are only 3 formulas, copied across.

Attachment 31630

Do check the values are correct.
06-10-2024, 05:44 PM
MasterBash

Quote:

Originally Posted by p45cal

Look at cells A5:E7 of the Sheet2 sheet in the attached.
It assumes you have static sets of values for the Service Level and Shipment columns, because the hard-coded values in the formula (circled in the pic below) represent what's included in the count. You can add/remove hard-coded values in those parts of the formula. They must be comma separated, regardless of which locale you're in.
There are only 3 formulas, copied across.

Attachment 31630

Do check the values are correct.

My god, this works ! I get the exact same resultats as when I did it manually. I am so grateful for your help. This will help the team a ton, because the report is quite big. :)

Thank you so much. :)