Power Query Problems

How to Create an Excel Timeline Using Fictional Year Numbers

John Wilson Sep 28, 2026 870 views

Question details

The user wants to filter a PivotTable using a Timeline slicer based on fictional or sequential year numbers (e.g., Year 1, Year 5) instead of standard calendar dates.

Create an Excel Timeline Using Fictional Year Numbers

Product: Microsoft Excel
Device & OS: not provided
Scenario: Building an interactive dashboard or PivotTable report to track events over a relative or fictional project timeframe.
Observed behavior: Excel's Timeline feature strictly requires standard date formats and cannot process text-based fictional years or integer sequences directly from the source data.

Before you start

Ensure your source data is formatted as an Excel Table (Ctrl+T). It helps to decide on a base real-world year (e.g., the year 2000) to act as an anchor so Excel's date engine can process your relative years correctly.

Solution 1Recommended

Convert Fictional Years to Dates using Power Query

Use Power Query to dynamically generate a valid date column for your fictional years, which is then loaded into the Data Model for a PivotTable Timeline.

Excel Timelines require date-type fields to function. By leveraging Power Query, we can extract the integer from your fictional year data and map it to a mock calendar date behind the scenes without permanently altering your raw source data.

Load data into Power Query

Select your data table, navigate to the 'Data' tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.

Add a custom date column

Go to the 'Add Column' tab and click 'Custom Column'. Write a formula to extract the year number and generate a date, for example, using M code: #date(2000 + Number.From(Text.Select([Fictional Year], {"0".."9"})), 1, 1).

Set the data type

Click the data type icon on the header of your newly created custom column and change it to 'Date'.

Load to Data Model

Click 'Home' > 'Close & Load To...'. In the import dialog, select 'PivotTable Report' and ensure you check the box that says 'Add this data to the Data Model'.

Insert the Timeline slicer

In your new PivotTable, arrange your fields as needed. Then, go to 'PivotTable Analyze' > 'Insert Timeline' and select your newly created custom date column to begin filtering.

Convert Fictional Years to Dates using Power Query

Data Model Advantage: Loading into the Data Model not only enables the Timeline but also allows you to write powerful DAX measures to aggregate events over your fictional timeframe.

Use WPS Spreadsheet to Create Timelines with Custom Dates

While Power Query is one method, you can easily achieve the exact same timeline functionality in WPS Office by adding a helper column with a simple formula, then creating a standard PivotTable.

1. Insert a helper column: Insert a new column next to your fictional years data and name the header 'Real Date'.
2. Apply a date conversion formula: Type a formula like =DATE(2000+VALUE(SUBSTITUTE(A2,"Year ","")), 1, 1) to convert strings like 'Year 5' into a standard date format (e.g., Jan 1, 2005) and drag it down.
3. Create a PivotTable: Highlight your entire data range including the new column, click 'Insert' > 'PivotTable', and place it on a new worksheet.
4. Insert Timeline: Click anywhere inside the PivotTable, go to the 'Analyze' tab, click 'Insert Timeline', and check your 'Real Date' helper column to enable chronological filtering.

Free and lightweight alternative to Microsoft ExcelHighly compatible with .xlsx PivotTables and TimelinesCreate custom date fields easily using familiar formula toolsSeamless data analysis without complex query languages

microsoft office alternative - wps office

Frequently Asked Questions

Why does the Excel Timeline feature require a date formatted field?

Timelines are specialized slicers built exclusively to group and filter chronological data logically (by days, months, quarters, and years). They rely on Excel's internal calendar system and cannot automatically parse standard text strings or plain integers.

Can I display the fictional year names on the Timeline instead of real dates?

The timeline slicer itself will display the mapped real dates (like 2001, 2002). However, you can group the Timeline strictly by 'Years' to minimize confusion, and use custom number formatting in your actual PivotTable cells to mask the real dates as 'Year 1' or 'Year 2'.

Does converting the fictional year to a real date alter my original source data?

No. When using Power Query or a formula-based helper column, your original raw data remains completely intact. The new date column acts purely as a structural bridge to allow the PivotTable and Timeline features to function correctly.

Excel & Data Problems