How to Convert Number to Date in Power BI

It's a frustratingly common scenario: you import data from Excel into Power BI, and suddenly your perfectly good dates are showing up as strange five-digit numbers like 44197. Don’t worry, your data isn’t broken. This article will show you exactly why this happens and walk you through three different methods - from a simple click to powerful formulas - to convert those numbers back into proper, usable dates in Power BI.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Does Power BI Turn My Dates into Numbers?

The short answer is: that's how Excel thinks about dates. Instead of storing "December 31, 2020," Excel stores it as 44197. This number represents the total number of days that have passed since its official starting point, which is January 1, 1900. In Excel's world, January 1, 1900, is day 1, January 2, 1900, is day 2, and so on. This system is called a "serial number" format.

When you import data from an Excel file or a CSV exported from Excel, Power BI will sometimes see that column of five-digit numbers and default its data type to "Whole Number." While Power BI is usually smart enough to automatically detect and convert these, it can occasionally miss the mark, especially if the column contains a mix of data types or has blank cells. The good news is that fixing it is straightforward once you know how.

Method 1: The Quick Fix - Changing the Data Type

This is the easiest method and should always be your first step. Often, all you need to do is tell Power BI that the column contains dates, and it will handle the conversion for you automatically. This is best done in the Power Query Editor, which is where you shape and transform your data before it gets loaded into your report.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 1: Open the Power Query Editor

First, you need to open Power Query. From the main Power BI Desktop window, go to the Home tab on the ribbon and click on the Transform data button. This will launch a new window, which is the Power Query Editor.

Step 2: Find Your Column

In the Power Query Editor, you'll see a preview of your data tables. Find and select the query (on the left) that contains your date data. Then, scroll horizontally until you find the column full of numbers that are supposed to be dates.

Step 3: Change the Data Type

In the header of the column, you'll see a small icon that represents the current data type. Since it's a number, it will likely be an icon that says or resembles "123". Click on this icon.

A dropdown menu will appear with a list of different data types. Simply select Date from the list.

Step 4: Confirm and Apply

Power BI will attempt to convert the numbers. You should see the numbers in your column instantly transform into readable dates. If it all looks correct, click the Close & Apply button in the top-left corner of the Power Query Editor. This saves your changes and loads the cleaned data into your report model.

For most simple Excel imports, this method works perfectly. However, if the conversion fails or shows incorrect dates, you may need to use a more explicit approach with a formula.

Method 2: Using DAX to Create a Calculated Column

If you prefer not to go back into Power Query or if the simple type change didn't work, you can create a new date column directly in your Power BI data model using a DAX (Data Analysis Expressions) formula. This approach creates a new column and leaves your original data untouched.

The key here is understanding the "base date." Because of a quirk where Excel incorrectly assumes the year 1900 was a leap year, the base date we need to use in our calculation is December 30, 1899. We then add the serial number to this base date.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step-by-Step Guide for the DAX Method

1. Go to the Data View: In the main Power BI Desktop window, click the table icon on the far left side to enter the Data View.
2. Select Your Table: Choose the table containing the number column from the right-hand Fields pane.
3. Create a New Column: On the ribbon at the top, navigate to the Table Tools tab (if it's not already selected) and click New column.
4. Enter the DAX Formula: A formula bar will appear. Here, you'll enter the formula to convert the number. Replace YourTable[NumberColumn] with the actual name of your table and column.

Corrected Date = DATE(1899, 12, 30) + YourTable[NumberColumn]

This formula tells Power BI to start at December 30th, 1899, and add the number of days specified in your column to that date.

1. Set the Data Type and Format: Press Enter. The new column will appear, but it might have a date and time format. To fix this, select the new "Corrected Date" column. In the Column Tools ribbon, change the Data type: to Date. You can also choose your preferred date format from the Format: dropdown menu (e.g., d/mm/yyyy).

You now have a clean, correct date column ready to use in your visuals and calculations, created without ever leaving the main report interface.

Method 3: Using M in Power Query for a Robust Transformation

For data modeling purists and for the most reliable transformations, the best place to handle conversions is in the Power Query Editor. Performing this step here cleans the data before it's even loaded, leading to a more efficient and cleaner data model. Instead of relying on the automatic type change (Method 1), you can write a simple formula using the M language to create a custom column.

This method gives you complete control and is the recommended best practice for cleaning data on import.

Step-by-Step Guide for the Power Query M Method

1. Open Power Query Editor: Just like in Method 1, go to the Home ribbon and click Transform data.
2. Select the Table: Select the relevant query from the list on the left.
3. Add a Custom Column: Navigate to the Add Column tab on the ribbon and click Custom Column.
4. Create the Conversion Formula: A new window will pop up. In this window, you will name your new column (e.g., "InvoiceDate") and enter the following M formula. Remember to replace [YourNumberColumn] with the name of the column you are trying to convert.

#date(1899, 12, 30) + #duration([YourNumberColumn], 0, 0, 0)

Formula Breakdown:

• #date(1899, 12, 30): This explicitly sets the starting "zero point" date, just like in the DAX formula.
• #duration(...): This function creates a duration value from the number in your column. The parameters are (days, hours, minutes, seconds), so we use our number column for the 'days' part and zero for the rest.

1. Set the Data Type: After clicking OK, the new column will appear. Its data type might be set to "Any". Click the "ABC 123" icon in the new column's header and change it to Date to finalize the transformation.
2. Clean Up (Optional): For a cleaner model, you can now remove the original column with the serial numbers. Right-click the header of the original number column and select Remove. You might also want to rename your new column to match the original's name.
3. Close & Apply: When everything looks right, click the Close & Apply button to load your newly transformed data.

Bonus Case: Handling Dates Stored as Numbers Like YYYYMMDD

Sometimes, dates aren't stored as Excel serial numbers but as concatenated integers, like 20231225 for December 25, 2023. The methods above won't work for this format, as this is just a large number, not a count of days. To fix this, you need to parse the number as text.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Quick Power Query Fix for YYYYMMDD Format

The most user-friendly way to fix this in Power Query is using the "Column From Examples" feature.

1. In the Power Query Editor, go to the Add Column tab and select Column From Examples.
2. A new, blank column will appear. Select a cell in this new column and simply start typing the date as you want it to appear based on the original number. For a row with 20231225, you might type 25/12/2023.
3. After you type one or two examples, Power Query's smart logic will detect the pattern you're creating and fill in the rest of the column for you.
4. If the preview looks correct, click OK. Finally, set the data type of this new column to Date.

DAX Formula for a YYYYMMDD Format Number

If you need a DAX calculated column instead, you'll need a formula that can read the components (year, month, and day) from the number. To do this, you first must ensure Power BI sees the column as text, then parse it.

FormattedDate = 
VAR DateAsText = FORMAT([YourNumberColumn], "0")
RETURN
DATE(
    LEFT(DateAsText, 4),    // First 4 characters for Year
    MID(DateAsText, 5, 2),  // Next 2 characters for Month
    RIGHT(DateAsText, 2)    // Last 2 characters for Day
)

Final Thoughts

Converting numbers to dates is a fundamental data cleaning step in Power BI that every analyst encounters. Whether you prefer a quick data type change, a flexible DAX formula, or a robust Power Query transformation, the key is understanding that the numbers are just Excel's way of counting days. By using the right base date - December 30, 1899 - you ensure your conversions are accurate every time.

We know that juggling these data formats and transformations between tools like Excel, Power BI, and dozens of other platforms is a recurring headache that slows teams down. We built Graphed to eliminate this friction entirely. After connecting your data sources in a few clicks, you can simply ask questions in plain English like, "show me my sales revenue by month" and Graphed automatically builds the live dashboard for you, handling all the data formatting and conversions in the background so you can get straight to the insights.