Count of data between 2 dynamic dates

[dmfweb]

If I have a large amount of data (ticket volume) and need to know how many tickets were created between 0-30 days ago, 31-60 days ago, 61 - 90 days ago, and 90+ is there an easy way to do with a pivot table or do I need to change the date filter by 30 days and copy and paste the data for each chunk?

IDEALLY.... I would love to have it done somewhat dynamically so when I pull the data weekly I wouldn't have to do so much manual work.

Thank you so much in advance.

--David

 

[Legacy 436357]

I have an option for you to choose your date range:

Excel Workbook
	A	B	C	D	E
1	Date	Tickets Sold
2	12/19/2018	50		Start Date	1/15/2019
3	1/1/2019	20		End Date	2/15/2019
4	1/15/2019	65
5	1/30/2019	88		Sum values between dates
6	2/2/2019	22		395
7	2/15/2019	220
8	11/19/2018	100
Sheet1

 

[Jeffrey Mahoney]

I am not a big fan of pivot tables. I use preformatted tables with formulas. I won't go into too much why.

When I have to repeat a report table over many times, All I have to do is copy my new report (database from SAP or other sources) and replace the old one. The formulas automatically adjust for the new number of rows because I use Dynamic named ranges for each column of data. SUMIFS and COUNTIFS are perfect for this. A simple COUNTIFS for your 61-90 days would be:
=Countifs(TicketDateCol,"<"&D1-60,TicketDateCol,">"&D1-91)
Where Cell D1 contains the date you want to compare. It could simply be today's date all the time.

Once I have the final format of my table I don't have to worry about it changing. It also can be more dynamic than a pivot table. Obviously there is some up front cost in terms of time, but the time savings in the future is dramatic.

Jeff

 

[dmfweb]

I am not a big fan of pivot tables. I use preformatted tables with formulas. I won't go into too much why.

When I have to repeat a report table over many times, All I have to do is copy my new report (database from SAP or other sources) and replace the old one. The formulas automatically adjust for the new number of rows because I use Dynamic named ranges for each column of data. SUMIFS and COUNTIFS are perfect for this. A simple COUNTIFS for your 61-90 days would be:
=Countifs(TicketDateCol,"<"&D1-60,TicketDateCol,">"&D1-91)
Where Cell D1 contains the date you want to compare. It could simply be today's date all the time.

Once I have the final format of my table I don't have to worry about it changing. It also can be more dynamic than a pivot table. Obviously there is some up front cost in terms of time, but the time savings in the future is dramatic.

Jeff

Thank you!! Finally an easy solution that just works.

--David