timeline slicer for start and end date Excel

**scalise0313** · 06-16-2022, 09:43 AM

Thank you in advance for your time and help. I am building an excel spreadsheet to track my utilization. So I am showing my % of time utilized for each task that fits into 4 categories and then projecting those categories on a bar chart, I am also tracking the start and end dates for each task. I want to make this able to have a timeline slicer so I can forecast my utilization for the quarter(s) ahead. The problem is when I use the start and end date as slicers they are not dynamic to realize that if something starts in Q1 and ends in Q3 that it should also be shown in Q2 etc. I am looking for some help because it seems like this should be something that is easy but I am lost. I am very familiar with VBA however, I would like to have this sheet without VBA use. Right now all of my data is in a table that feeds a pivot table for the pivot chart that displays my utilization. Attached is a picture of my table.

table.png

**GlennUK** · 06-18-2022, 07:30 AM

Your requirement is not suitable for slicers. You need to create a formula to calculate utilisation on a particular date, and then feed that into a Data Table, and have the results build over the quarters ahead. Do you see what I mean?

**scalise0313** · 06-29-2022, 10:50 AM

I am not sure what you mean do you have or know of an example that would help clarify?

**JeteMc** · 07-04-2022, 07:09 PM

The closest thing that I can think of to what you are asking is:
1. Add four columns, one per quarter, populated using: =OR(AND($B3>=E$1,$B3<EDATE(E$1,3)),AND($C3>=E$1,$C3<=EDATE(E$1,3)),AND($B3<=E$1,$C3>=EDATE(E$1,3)))
2. Add data validation (list) to a cell (L1)
3. Populate the categories and percentages using: =IFERROR(INDEX(A$3:A$8,AGGREGATE(15,6,(ROW(A$3:A$8)-ROW(A$2))/($E$2:$H$2=$L$1)/($E$3:$H$8=TRUE),ROWS(K$3:K3))),"")
For more specific help please utilize the information in the banner at the top of the page to upload the Excel file from which the screenshot in post #1 is taken.
Let us know if you have any questions.