Excel - Pivot Table - Grayed Out Timeline Filter
Nakee_Jr
Posted messages
5
Status
Member
-
Nakee_Jr Posted messages 5 Status Member -
Nakee_Jr Posted messages 5 Status Member -
Hello everyone,
I work with Excel 2016 and I have a problem with my pivot table; the timeline filter button does not work and I can only sort by selecting all the dates.
I export my reservation data to Excel:
Each row consists of:
- Service name
- Event date
- Pre-reservation date
- Reservation date
- Price paid online
- Total price
- Promo code
The first problem was that each date was in the format e.g., 2017-06-14 14:20:32. I tried the cell format yyyy-mm-dd hh:mm:ss but it didn’t work at all, so I separated it into 2 distinct columns using the convert tool from the data tab.
I am a beginner in Excel, at least in the more advanced use of Excel.
1. My first question is how to add data to my raw data tab so that it is still included in this table and also taken into account later in a pivot table?
2. Additionally, if I import this data, my date data will be grouped in the initial format, meaning the day and the time. Is there a solution to automate this? Because I analyze my data daily and I don’t have time to redo this every time...
3. What really bothers me is when I create my pivot table. I refer to "Table1"; everything works except that I do not have access to the timeline filter button. Despite a lot of research, I can't find a solution.
Option 1: I keep the Day and Time format and use the filters. This solution is not feasible because I need to have 70/80 lines/day.
Option 2: Same with the columns separated. So filters, etc.
I have seen that the cell format could be the cause of the problem, but by separating the columns I have a date format that has been automatically set (I forgot to mention I replaced the - with / to make this happen).
I hope I explained myself well :) Thank you in advance for your priceless help.
I work with Excel 2016 and I have a problem with my pivot table; the timeline filter button does not work and I can only sort by selecting all the dates.
I export my reservation data to Excel:
Each row consists of:
- Service name
- Event date
- Pre-reservation date
- Reservation date
- Price paid online
- Total price
- Promo code
The first problem was that each date was in the format e.g., 2017-06-14 14:20:32. I tried the cell format yyyy-mm-dd hh:mm:ss but it didn’t work at all, so I separated it into 2 distinct columns using the convert tool from the data tab.
I am a beginner in Excel, at least in the more advanced use of Excel.
1. My first question is how to add data to my raw data tab so that it is still included in this table and also taken into account later in a pivot table?
2. Additionally, if I import this data, my date data will be grouped in the initial format, meaning the day and the time. Is there a solution to automate this? Because I analyze my data daily and I don’t have time to redo this every time...
3. What really bothers me is when I create my pivot table. I refer to "Table1"; everything works except that I do not have access to the timeline filter button. Despite a lot of research, I can't find a solution.
Option 1: I keep the Day and Time format and use the filters. This solution is not feasible because I need to have 70/80 lines/day.
Option 2: Same with the columns separated. So filters, etc.
I have seen that the cell format could be the cause of the problem, but by separating the columns I have a date format that has been automatically set (I forgot to mention I replaced the - with / to make this happen).
I hope I explained myself well :) Thank you in advance for your priceless help.
1 answer
Hello,
1) If you refer to "Table1", by refreshing you will have the entered data, rows, and columns.
2) The import must be done in text: try doing a special paste multiplying by 1.
3) I didn't understand your question.
--
Always zen
Perfection is achieved, not when there is nothing more to add, but when there is nothing left to take away. Antoine de Saint-Exupéry
1) If you refer to "Table1", by refreshing you will have the entered data, rows, and columns.
2) The import must be done in text: try doing a special paste multiplying by 1.
3) I didn't understand your question.
--
Always zen
Perfection is achieved, not when there is nothing more to add, but when there is nothing left to take away. Antoine de Saint-Exupéry
1. I thought that adding Table 1 would be enough because adding rows underneath inserts them into it, so thank you for the confirmation.
2. As soon as I import the data, I have to do it with the special paste option, right?
3. I expressed myself poorly... The question is why my "insert timeline" button in the "Analyze" tab of the pivot table is grayed out. This must be related to question 2, right? If the dates are incorrectly configured, they won't be taken into account for the latter.
Best regards,