If you have ever opened a spreadsheet and found a column of five-digit numbers like 45000 where dates should be, you need to convert excel date number to date values, and the fix takes seconds. Excel stores every date as a serial number, a count of days since a starting point. The number is not an error; it is how the program works internally. This guide explains the logic, then walks through formatting, formulas, Power Query, and troubleshooting for every common scenario.

Think of the serial number as an odometer reading. In the default Windows system, the number 1 represents January 1, 1900, the number 2 represents January 2, 1900, and so on. That makes 45000 equal to March 15, 2023. Because dates are plain numbers, Excel can add, subtract, and compare them. Subtract one date from another and you get the days between them. This design is powerful, but it explains why a cell sometimes shows digits instead of a calendar date.

The most common cause of the problem is formatting. A cell formatted as General or Number shows the raw serial value, even when the underlying data is perfectly valid. This happens after importing a CSV file, pasting from a database export, or copying results from a formula that returns a date. In that case nothing is broken, and changing the number format reveals the date instantly. Other causes, such as text-stored numbers or Unix timestamps, need a formula instead, and we cover those below.

Knowing which situation you face saves time. If the number is between about 1 and 2,958,465 and sits in a cell that right-aligns, it is almost certainly a real serial number needing only a date format. If it is left-aligned, it may be text. If it has ten or thirteen digits, it is likely a Unix timestamp in seconds or milliseconds. Each case has a different fix, and this article gives you a clear decision path for all of them.

Accuracy matters because dates drive reports, pivot tables, and lookups. A misread serial number can shift a monthly total into the wrong period or break a vlookup excel formula that matches on date keys. A column that looks like dates but is secretly text will fail to sort chronologically, and filters will not group by year or month. Converting correctly once, at the start of your workflow, prevents a long chain of subtle errors later in your analysis.

You do not need macros or add-ins for any of this. Everything here uses built-in features available in Excel for Windows, Mac, and Excel for the web, with notes where behavior differs. Work through the sections in order if you are new to the topic, or jump to the one that matches your data. By the end, you will be able to turn any date number into a clean, sortable, correctly formatted date with confidence.

The fastest fix requires no formula at all. Select the cells that hold the serial numbers, press Ctrl+1 to open the Format Cells dialog, choose the Date category, and pick a style such as 3/14/2012. Click OK and 45000 becomes 3/15/2023 immediately. The underlying value never changes, so every calculation continues to work. Use this method whenever your numbers are genuine serials that only need a calendar appearance on screen, which covers most imports and pasted reports.

You can also use the ribbon. On the Home tab, open the Number Format dropdown in the Number group and choose Short Date or Long Date. Short Date follows the regional settings of your operating system, so a US computer displays month, day, year, while a European setup may reverse the order. Long Date spells out the weekday and month, producing something like Wednesday, March 15, 2023. Both options leave the stored number untouched, which makes them reversible at any time.

Keyboard shortcuts make bulk formatting quicker. Press Ctrl+Shift+# to apply the day-month-year style with an abbreviated month, such as 15-Mar-23, and Ctrl+Shift+~ to return to General if you want to see the raw serial again. If you need a layout that is not in the list, open Format Cells, select Custom, and type a code like mm/dd/yyyy, mmm d, yyyy, or dddd. Custom codes give you complete control without changing any data.

Remember that serial numbers can include a decimal portion representing time. The value 45000.75 equals March 15, 2023 at 6:00 PM, because 0.75 is three quarters of a day. A date-only format hides the time, while a format such as mm/dd/yyyy hh:mm AM/PM shows both. If your report needs only the date, wrap the cell in the INT function to strip the fraction, which prevents mismatches when you compare or look up dates later.

Watch out for the row of pound signs. If a cell shows ##### after you apply a date format, the column is usually too narrow, so double-click the right edge of the column header to autofit it. The same symbols appear when a result is negative, because the standard Windows system cannot display dates before January 1, 1900. In that case, check your subtraction or the source number, since a negative serial almost always signals a mistake upstream.

To copy formatting to other columns, use Format Painter on the Home tab, or paste with Paste Special and choose Formats. If the same sheet will be refreshed often, apply the date format to the entire column before pasting new data, so incoming numbers display correctly right away. Converting a range into an Excel Table helps too, because new rows inherit the column's format automatically. These small habits remove repetitive cleanup and keep every report consistent from week to week.

When formatting alone does not work, a formula gives you control. If the serial number sits in A2, the simplest formula is =A2 with a date format applied, but you can also output text with =TEXT(A2,"mm/dd/yyyy"). The distinction matters: TEXT returns a text string that looks like a date but cannot be sorted chronologically or used in date math. Use TEXT only for labels in reports, and keep a true date column elsewhere for calculations and filtering.

Text-stored numbers are the second big culprit. A cell that shows 45000 with a green triangle in the corner and left alignment contains text, not a number. Format changes will not affect it. Fix it with =VALUE(A2) or =A2*1, then apply a date format to the result. For a whole column, select it, use Data then Text to Columns, and click Finish immediately, which forces Excel to re-evaluate every entry as a number in one step.

Dates stored as eight-digit numbers such as 20230315 are common in database exports. Excel reads them as the number twenty million, not a date. Build the real date with =DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)). The DATE function accepts a year, month, and day and returns the correct serial number. Because LEFT, MID, and RIGHT return text, DATE converts them automatically. Apply a date format afterward, and the column is ready to sort, filter, and pivot.

Unix timestamps count seconds since January 1, 1970. A ten-digit value such as 1678838400 converts with =A2/86400+25569, where 86,400 is the number of seconds in a day and 25,569 is the number of days between Excel's starting point and the Unix epoch. For thirteen-digit millisecond values, divide by 86,400,000 instead. That example returns March 15, 2023. Format the result as a date or date-time, and remember that the answer is in UTC unless you add your offset.

Text dates like March 15, 2023 or 2023-03-15 can be converted with =DATEVALUE(A2). DATEVALUE returns the serial number, which you then format. It depends on your regional settings, so a string such as 03/04/2023 may be read as March 4 or April 3 depending on the computer. When your source mixes layouts, split the parts with Text to Columns using the Date option and choose MDY, DMY, or YMD explicitly for reliable results.

Once the helper column returns correct dates, convert the formulas into values so the original column can be deleted. Copy the helper column, right-click the destination, choose Paste Special, and select Values. Then apply the date format again if needed. This step also speeds up large workbooks, because thousands of live formulas slow recalculation. Always keep a backup of the original data on a separate sheet before you overwrite anything, in case a conversion needs to be repeated.

For recurring imports, Power Query is the best long-term solution. Select your data, open the Data tab, and choose From Table/Range. In the editor, click the column header icon on the left, choose Date, and Power Query converts the values. If the column holds serial numbers, add a custom column with =Date.From([Serial]). Click Close and Load, and every future refresh applies the same transformation automatically, so you never repeat the cleanup by hand.

Power Query treats serial numbers the same way Excel does, but it adds useful safeguards. Rows that cannot be converted show an Error instead of a wrong date, which helps you spot bad data quickly. You can also set the column's locale by right-clicking the header, choosing Change Type, and then Using Locale. This lets you tell Excel that a text date follows the UK or US convention, avoiding the classic day-month confusion in international files.

The 1904 date system is an edge case worth knowing. Older versions of Excel for Mac started counting from January 1, 1904, and some workbooks still use that setting. If dates look exactly four years and one day off after you copy data between files, this is why. The difference is 1,462 days. Check File, Options, Advanced, and look under When calculating this workbook for the Use 1904 date system box before you adjust anything manually.

Excel also carries a historical quirk: it treats 1900 as a leap year, so it contains a February 29, 1900 that never existed. The bug was kept for compatibility with Lotus 1-2-3. For modern dates after March 1, 1900, the effect is invisible, and your conversions are accurate. It matters only if you work with very early dates or compare Excel's results to another program, such as a database or programming language, that counts days correctly.

Upper and lower limits are also worth remembering. The largest valid date is December 31, 9999, which equals the serial number 2,958,465. Anything larger shows ##### or returns a #VALUE! error. Decimal values are fine, but negative numbers are not valid in the 1900 system. If you see a six-digit number that seems too large, check whether it is actually a time-stamped value, an ID, or a concatenated code rather than a true date.

Web and cloud tools can behave differently. Excel for the web converts and formats dates the same way, and Google Sheets uses a similar serial system but starts counting from December 30, 1899. When you move files between the two, small offsets of one or two days can appear for early dates. For everyday dates in recent decades, both applications agree, so exporting and importing rarely cause trouble after you verify a few sample cells against known calendar dates.

Build a repeatable routine. First, inspect a few cells by selecting them and checking the formula bar and alignment. Second, decide whether the data is a real serial, text, or a timestamp. Third, apply the matching fix and verify three sample rows against a calendar. Fourth, format the column consistently. Writing these steps in a checklist saves time on every future import and helps teammates handle the same data without guessing or repeating your trial and error.

Protect your headers while you work. Scroll-heavy datasets are easier to check when the top row stays visible, so learn how to freeze a row in excel from the View tab by choosing Freeze Panes, then Freeze Top Row. With the headings pinned, you can scroll through thousands of converted dates and still know which column holds the original serial number and which holds the cleaned result, reducing mistakes during review.

Be careful with layout tools. Many people look up how to merge cells in excel to make titles look neat, but merged cells inside a date column break sorting, filtering, and pivot tables. Keep your data area in a plain grid, with one value per cell and one header per column. Use Center Across Selection if you want a centered title above the table without merging anything, which keeps the structure clean for formulas.

Dates also work well with validation. If users must enter dates by hand, learn how to create a drop down list in excel using Data Validation, or restrict a cell to a date range with the Date option. This prevents entries like 3.15.23 or March fifteenth, which Excel may store as text. Preventing bad input costs less than converting it later, particularly in shared workbooks edited by several people at once.

When matching dates between sheets, a vlookup excel formula needs both sides to be the same type. A true date on one side and a text date on the other returns #N/A even though they look identical. Test with =ISNUMBER(A2); TRUE means a real serial, FALSE means text. Using INT on date-time values also helps when the lookup table holds dates without times. Matching types first solves most lookup mysteries before they cost you an afternoon.

Finally, practice until the process feels automatic. Try converting 45000, 45292, and 46023 into dates by hand, then confirm with Excel: they are March 15, 2023, January 1, 2024, and January 1, 2026. Take timed quizzes on formulas and formatting to find gaps, and keep a small reference sheet of your favorite conversions. Skill with date handling pays off across nearly every Excel task, from budgeting to project scheduling.