Why dates turn into numbers after Power Query loads to Excel
Troubleshooting · By Tahir Nas · Examples run in our simulator · Updated
10 min read
Dates turn into numbers after loading because Excel stores every date as a serial number and only shows it as a date when the cell has a date format. When Power Query hands Excel a column without a Date type, or the table column is formatted as General, you see the number underneath, such as 46296 for 1 October 2026.
What the number actually is
Excel stores dates as sequential serial numbers so they can be used in calculations, and 1 January 1900 is serial number 1 in the default date system (DATE function, Microsoft Support). The same page says you need to change the number format to display a proper date. So 46296 isn't a broken value. It's 1 October 2026 shown without a date format.
Power Query uses the same idea when it converts a date to a number:
let
Source = #table(type table [OrderDate = date], {{#date(2026, 10, 1)}}),
AsNumber = Table.AddColumn(Source, "Serial", each Number.From([OrderDate]), type number)
in
AsNumberResult
| OrderDate | Serial |
|---|---|
| 2026-10-01 | 46296 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The Serial column shows 46296, the same number Excel stores for that day. A quick test in the sheet confirms the diagnosis: select one of the numbers and apply a Short Date format. If 1 October 2026 (or whatever date you expected) appears, the value is right and only the type or the format is missing.
A time of day shows up as a fraction. A Date/Time value is stored underneath as a decimal number (Data types in Power Query, Microsoft Learn), so 09:00 on 1 October 2026 appears as 46296.375.
Cause 1: the column's type is Any
The Microsoft Learn page on data types describes Any as the status given to a column that doesn't have an explicit data type, and recommends avoiding Any columns in the output of a query (Data types in Power Query, Microsoft Learn). A date column left as Any is the first thing to check when dates arrive in Excel looking like numbers. The test on your own file is quick: if the column's header icon in the editor is ABC/123 and the loaded cells show numbers, set a Date type, refresh, and look again.
Several ordinary steps produce an Any column without telling you.
A custom column added without a type
Add Column > Custom Column, or Table.AddColumn without its fourth argument, gives the new column type Any even when every value is a date:
let
Source = #table(type table [OrderID = text, OrderDate = date], {{"A1", #date(2026, 10, 1)}, {"A2", #date(2026, 10, 15)}}),
Added = Table.AddColumn(Source, "DueDate", each Date.AddDays([OrderDate], 30)),
Schema = Table.SelectColumns(Table.Schema(Added), {"Name", "TypeName"})
in
SchemaResult
| Name | TypeName |
|---|---|
| OrderID | Text.Type |
| OrderDate | Date.Type |
| DueDate | Any.Type |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
OrderDate is Date.Type, but DueDate is Any.Type. Passing the type as the last argument fixes it in the same step:
let
Source = #table(type table [OrderID = text, OrderDate = date], {{"A1", #date(2026, 10, 1)}, {"A2", #date(2026, 10, 15)}}),
Added = Table.AddColumn(Source, "DueDate", each Date.AddDays([OrderDate], 30), type date),
Schema = Table.SelectColumns(Table.Schema(Added), {"Name", "TypeName"})
in
SchemaResult
| Name | TypeName |
|---|---|
| OrderID | Text.Type |
| OrderDate | Date.Type |
| DueDate | Date.Type |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Now DueDate is Date.Type. The Table.AddColumn reference shows the optional type argument.
A table with no Changed Type step
Automatic detection adds a Changed Type step for unstructured sources such as Excel, CSV and text files, based on the first 200 rows, and it can be switched off in the options (Data types in Power Query, Microsoft Learn). If it's off, or you deleted the Changed Type step, or the table comes from a step you wrote by hand, every column is Any:
let
Source = #table({"OrderID", "OrderDate"}, {{"A1", #date(2026, 10, 1)}, {"A2", #date(2026, 10, 15)}}),
Schema = Table.SelectColumns(Table.Schema(Source), {"Name", "TypeName"})
in
SchemaResult
| Name | TypeName |
|---|---|
| OrderID | Any.Type |
| OrderDate | Any.Type |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Both columns are Any.Type, although the values in OrderDate are dates. Add a type step near the end with Table.TransformColumnTypes, and write the types yourself rather than relying on detection.
An append that mixes types
When two tables are appended and the same column holds dates in one and something else in the other, the result can only be Any:
let
Store = #table(type table [Invoice = text, InvoiceDate = date], {{"S1", #date(2026, 10, 1)}}),
Web = #table(type table [Invoice = text, InvoiceDate = number], {{"W1", 46297}}),
Combined = Table.Combine({Store, Web}),
Schema = Table.SelectColumns(Table.Schema(Combined), {"Name", "TypeName"})
in
SchemaResult
| Name | TypeName |
|---|---|
| Invoice | Text.Type |
| InvoiceDate | Any.Type |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
InvoiceDate is Any.Type after the append, and the web rows still hold the number 46297. Fix the type in each table before combining. The lesson on mixed column types after an append works through this case, and the exercise align column types before appending practises it.
Check every column at once
Table.Schema returns one row per column with its type name, which is faster than reading header icons on a wide table. The lesson Inspect column types and catch conversion errors shows how to filter it to the columns whose type is Any.
Cause 2: the source already holds serial numbers
Sometimes the numbers come from the source. An export from another Excel file, or a CSV saved from a sheet where the date column was formatted as General, contains 45931 rather than a date. The post on converting Unix timestamps and Excel serial dates covers more serial formats; the cases below are the ones that show up after a load.
Serials typed as numbers
If the column is already typed as a number, Change Type to Date converts it, the same way Date.From converts each value:
let
Source = #table(type table [Invoice = text, Serial = number], {{"I1", 45931}, {"I2", 45946}}),
Typed = Table.TransformColumnTypes(Source, {{"Serial", type date}})
in
TypedResult
| Invoice | Serial |
|---|---|
| I1 | 2025-10-01 |
| I2 | 2025-10-16 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The values become 1 October 2025 and 16 October 2025. Date.From reads a number as days before or after midnight, 30 December 1899 (Date.From, Microsoft Learn).
Serials stored as text
Serials that arrived as text are different. Change Type to Date tries to read "45946" as a written date and fails with "We couldn't convert to Date." Convert through Number.From first, which also copes with a column that mixes numbers and text:
let
Source = #table({"Invoice", "Serial"}, {{"I1", 45931}, {"I2", "45946"}}),
Dates = Table.TransformColumns(Source, {{"Serial", each Date.From(Number.From(_)), type date}})
in
DatesResult
| Invoice | Serial |
|---|---|
| I1 | 2025-10-01 |
| I2 | 2025-10-16 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The result is again 1 October 2025 and 16 October 2025, with the column typed as date. Only type ascription leaves values alone: the type argument of Table.AddColumn, or a type given to Table.TransformColumns with a function that doesn't convert, labels the column without changing what's in it.
Why the count starts at 30 December 1899
Why 30 December 1899 and not 1 January 1900? Excel deliberately treats 1900 as a leap year for compatibility with Lotus 1-2-3; Microsoft's article on it says the visible side effect is that WEEKDAY returns wrong values for dates before 1 March 1900, which is rare (Excel incorrectly assumes that the year 1900 is a leap year, Microsoft Learn). Working the arithmetic through, the earlier base in Date.From absorbs Excel's extra day, so from 1 March 1900 onwards both give the same serial number. Business data from recent decades lines up exactly.
Workbooks using the 1904 date system
Excel also has a 1904 date system, which counts from 1 January 1904 (Date systems in Excel, Microsoft Support). The same page gives the difference between the two systems as 1,462 days, so serials exported from a workbook that uses the 1904 system are 1,462 smaller, and Date.From would put every date four years and a day early. Add 1,462 before converting if you know the source uses that system.
Dates written out as text
If the dates arrive as text in a day-first or month-first layout instead, the fix is a locale, not Date.From on a number. The guide Change type using locale covers that, and the exercise convert day-first dates with the right locale practises it.
Cause 3: the Excel table column is formatted as a number
If the column is typed as Date in Power Query and still shows numbers, look at the sheet. A table column that was formatted as General or Number earlier, by hand or by a macro, can keep that format. Format the column as a date (Home > Number Format, or Format Cells), then make sure refreshes keep it.
Microsoft's page on external data ranges lists two options in the Data formatting and layout section of the properties dialog (Manage external data ranges and their properties, Microsoft Support):
- Preserve cell formatting, to keep the cell formatting you apply.
- Preserve column sort/filter/layout, to keep the sorting, filtering and layout you apply.
The page says the dialog opens from Properties on the Data tab, or from the connection's Properties command in Connections & Queries. If dates revert to numbers after each refresh, check that both are ticked.
Quick diagnosis table
| What you see | Likely cause | Fix |
|---|---|---|
| Numbers such as 46296, header icon ABC/123 in the editor | Column type is Any | Set type date on the last step that touches the column |
| Decimals such as 46296.375 | Date/Time value, cell not formatted | Format as date and time, or convert to Date if you don't need the time |
| Numbers even in the editor, column type is number | Source holds serial numbers | Change type to Date; for serials stored as text, Date.From(Number.From(_)) |
| Dates in the editor, numbers only in the sheet | Table column formatted as General or Number | Format the column, tick the preserve options |
| Some rows dates, some numbers | An append mixed types | Type each table before combining |
Make the type part of the query, not a one-off fix
The durable fix is to give every output column an explicit type on the final steps, so the query describes its own output. A short type step at the end, written by hand, survives changes upstream better than the automatic Changed Type step that sits near the top. That automatic step also hard-codes every column name, which is why it is often the first step to fail when a source renames its column headers. If the query feeds a report someone else refreshes, add a check that fails when a date column comes out as Any; the lesson Validate data and fail loudly shows the pattern, and the exercise fix a Changed Type step that breaks two columns is good practice for reading and repairing type steps.
Frequently asked questions
Does loading to the Data Model have the same problem?
Not the cell-format half of it, because the model has no worksheet cells. The type half still applies: the Microsoft Learn page on data types says a Date is stored in the model as a Date/Time with zero for the time part, and the same page recommends avoiding Any columns in a query's output. Set the Date type in the query and the model receives a date column. The post on choosing between a worksheet table and the Data Model covers the wider choice.
Can I format a date in Power Query so Excel keeps it?
Power Query sets the type of a column; Excel decides how the cell looks. Converting the date to text with Date.ToText makes it look right, but Excel then treats it as text, so it won't sort or filter as a date. Keep the Date type in the query and set the display format in Excel.
Why does 1 January 1900 come out a day different?
Because Excel counts 29 February 1900, a day that never existed, while Date.From starts from 30 December 1899. Serial 1 is 1 January 1900 in Excel and 31 December 1899 in Power Query. From 1 March 1900 onwards the two line up.
To practise typing and converting dates on realistic data, the Power Query Exercises set on numbers and dates runs your query in the browser and checks the result, types included.
Sources
- DATE function, Microsoft Support (support.microsoft.com)
- Data types in Power Query, Microsoft Learn (learn.microsoft.com)
- Date.From, Microsoft Learn (learn.microsoft.com)
- Excel incorrectly assumes that the year 1900 is a leap year, Microsoft Learn (learn.microsoft.com)
- Date systems in Excel, Microsoft Support (support.microsoft.com)
- Manage external data ranges and their properties, Microsoft Support (support.microsoft.com)
About the author
Tahir Nas, software engineer, writes and reviews the lessons, guides and exercises on Power Query Exercises. Tahir has 8 years of experience in tech, working across system administration, cloud architecture, QA, scripting and automation, and software engineering in many languages and mainstream databases. GitHub profile. Every M example shown with a result was run in the site's simulator when the page was built, and the output is that run's result; examples marked for Excel and Power BI only are shown without output. How content is tested.
Keep reading
- Convert Unix timestamps and Excel serial dates in Power QueryTurn Unix epoch seconds or milliseconds and Excel serial numbers like 46023 into dates in Power Query, with tested M for text, nulls and 1904 workbooks.
- Why your Excel report breaks every month, and how Power Query fixes itMonthly Excel reports break when the export changes shape. See the usual causes and the Power Query steps that let a report survive each refresh.
- Case sensitivity in Power Query: the silent cause of missing matchesPower Query treats upper and lower case as different, so Ben@Shop.com won't match ben@shop.com. Fix merges, filters and groups with a key or a comparer.
Practise Power Query in the browser with graded exercises checked against fresh data.
Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.