Forum Discussion
Problems with creating a ‘Time Scale‘
I have a dynamic chart fed by a Power Query query based on a table from another Excel file.
In this query there are two date fields: “Fecha” and “F.Previsión”
If I try to perform a Time Scale with the “Fecha” field no problem arises; but if I try to perform it with the “F.Previsión” field it shows me the following error: Microsoft Excel cannot create a time scale because it cannot get the required information from the date field.
However, if I enter the field in the “Axis (Categories)” of the chart, it groups it correctly in: Years, Quarters, Months and Days:
So I understand that Excel correctly interprets that it is a date field.
Can anyone give me any ideas or suggestions for the chart to create the Time Scale for that field?
Spanish:
Tengo un gráfico dinámico alimentado por una consulta de Power Query basada en una tabla de otro fichero Excel.
En esta consulta existen dos campos de fecha: "Fecha" y "F.Previsión".
Si trato de realizar una Escala de tiempo con el el campo "Fecha" no surge ningún problema; pero si lo trato de realizar con el campo "F.Previsión" me muestra el siguiente error:
Sin embargo, si introduzco el campo en el "Eje (Categorías)" del gráfico, me lo agrupa correctamente en: Años, trimestres, meses y días:
Por lo que entiendo que Excel interpreta correctamente que se trata de un campo de fecha.
¿Alguien puede darme alguna idea o sugerencia para que el gráfico pueda crear la Escala de tiempo de dicho campo?
Muchas gracias por la ayuda
2 Replies
1. Check the data format:
Select the time data column, right-click and select “Set Cell Format”.
In the Numbers tab, select Time or Custom to ensure the format is correct (e.g. HH:MM or HH:MM:SS).
2. Use the correct chart type:
Select the data range and click “Insert” > “Line Chart” or “Bar Chart”.
Make sure that the time data is on the horizontal axis (X-axis).
3. Set the horizontal axis to the time scale:
Right-click on the horizontal axis of the chart and select “Set Axis Format”.
In “Axis Options”, make sure that “Axis Type” is set to “Date Axis”.
Adjust the Units and Major Scale to match the time data.
4. Check the data range:
Make sure that the selected data range contains time data and corresponding numeric data.
If the data is not continuous or contains null values, the time scale may not be displayed correctly.
5. Use the pivot table:
Select the data range and click Insert > Pivot Table.
Drag the time fields to the Rows area and the numeric fields to the Values area.
Create a pivot chart and set the horizontal axis to the timescale.
6. Update Excel:
Click File > Accounts > Update Options > Update Now.
7. Fix Office:
Open “Control Panel” > “Programs and Features”, locate Microsoft Office, right-click and select “Change” > “Repair” and restart your computer.First of all, I would like to thank you for your response.
But, after following all the steps indicated, the problem remains. Even though Office 365 recognizes the date format of the field, it gives an error when trying to insert a time scale in a pivot chart
