Excel Date Converter: Turn Text, Numbers, and Timestamps

How-to basics · Updated November 18, 2026

An Excel date converter turns date-like text or numbers into usable Excel dates, or converts Excel dates into a different format. For text such as 3/14/2025 in A2, enter =DATEVALUE(A2) in another cell and apply a date format; if the value is already a serial number, change its number format instead of converting it with a formula.

What an Excel date converter does

“Excel date converter” can refer to several different tasks: making text into a date Excel can calculate with, displaying a valid date in a new style, converting a serial number to a readable date, or translating a timestamp from another system. The right method depends on what is actually in the cell, not just on how it looks.

A cell showing 14-Mar-2025 might contain a genuine Excel date, a text string, or a number with a custom display format. Those are not interchangeable. Genuine dates can be sorted chronologically, used in date arithmetic, and recognized by functions such as YEAR and MONTH. Text that resembles a date may appear in the right order but fail in calculations or sort alphabetically. A converter should therefore preserve the intended calendar date and produce a value Excel recognizes when that is the goal.

Start by selecting a cell and checking the formula bar and the Number Format list on the Home tab. If a date displays as a five-digit number after you change its format to General, it is probably an Excel date serial. If changing the format does nothing and the entry is left-aligned by default, it may be text, though alignment alone is not conclusive.

How Excel stores dates and times

Excel stores dates as serial numbers: the whole-number part represents days, and the fractional part represents time within a day. In the standard 1900 date system, for example, a date with a time is a number with a decimal fraction. Formatting changes how that number appears; it does not change the underlying value.

This explains why typing a date into a cell and seeing a number is not necessarily an error. Select the cell, then choose a date style from Home > Number Format, or open Format Cells with Ctrl+1 on Windows. On Mac, Command+1 opens Format Cells. Choose a date format or create a custom one, such as yyyy-mm-dd, to display the date consistently.

Excel also has a historical compatibility quirk: its 1900 date system treats 1900 as a leap year even though it was not. Serial number 60 represents the fictitious date February 29, 1900. This rarely affects ordinary modern dates, but can matter when exchanging very old dates or comparing data with systems that use a different date convention. Workbooks can also use the 1904 date system; the same calendar date has serial numbers 1,462 days apart between the two systems. Check the workbook’s date-system setting before interpreting or moving serial numbers between files.

Convert date text into a real Excel date

If Excel recognizes the text according to your regional settings, DATEVALUE is a straightforward conversion. With text 3/14/2025 in A2, enter =DATEVALUE(A2) in B2; the result is the serial number 45730, which you can format as a date. This example assumes that the text means March 14, 2025. In a region that interprets that pattern as day/month/year, the same text may be ambiguous or return an error.

For dates stored in separate year, month, and day cells, construct a date from the components rather than joining them as text. With the year in A2, month in B2, and day in C2—for example, 2025, 3, and 14—enter =DATE(A2,B2,C2). The result is March 14, 2025, as a date value. DATE also normalizes out-of-range components: for instance, a day that exceeds the number of days in a month rolls into the following month, so check source data if that behavior is not intended.

For a consistent eight-character value such as 20250314, parse the year, month, and day by position. With that text in A2, enter =DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)). The result is March 14, 2025. This approach avoids relying on whether the computer interprets a slash-separated date as month-first or day-first, provided every source value follows the same YYYYMMDD pattern.

Change how a valid date is displayed

If the cell already contains a valid date, there is usually no need to convert its value. Select the cells and choose a display style from Home > Number Format. For more control, open Format Cells and select Custom. Common format codes include m/d/yyyy for a month-first display, dd/mm/yyyy for a day-first display, and yyyy-mm-dd for an unambiguous year-month-day display. In format codes, m can represent a month or, in a time context, minutes; Excel interprets it based on the surrounding date and time codes.

A format change is preferable when calculations must continue to work. By contrast, TEXT returns a text string. With an Excel date in A2, enter =TEXT(A2,"yyyy-mm-dd") in B2 to return text such as 2025-03-14. That can be useful for building a fixed-format export or a label, but the result is no longer a date value. Use the original date cell for calculations, or convert the text back before relying on date arithmetic.

Be cautious with copying and pasting values. A pasted date may retain its serial value but take on the destination cell’s format, or it may arrive as text depending on the source application and import method. Confirm both the displayed date and whether Excel can perform a simple date calculation with it.

Convert Excel dates to Unix timestamps

Unix timestamps commonly count seconds from January 1, 1970 at 00:00 UTC. Excel dates count days from a different starting point, so conversion requires scaling days into seconds and accounting for the different origins. With a valid Excel date-time in A2, enter =(A2-DATE(1970,1,1))*86400 to return Unix seconds. For a date at midnight on March 14, 2025, the result is 1741910400.

To convert Unix seconds back to an Excel date-time, with the timestamp in A2 enter =A2/86400+DATE(1970,1,1). For 1741910400, the result is March 14, 2025 at midnight when displayed with a date and time format. If the input timestamp is in milliseconds, divide by 86400000 rather than 86400. Confirm the source’s unit before converting; a seconds-versus-milliseconds mismatch creates a dramatically incorrect result.

Time zones are a separate issue from date serial conversion. Excel date-time values do not store a time-zone label or automatically apply daylight-saving rules. A timestamp defined as UTC should be interpreted as UTC unless you deliberately adjust it for a particular location. If data mixes local times and UTC, establish the source convention before comparing or converting records.

Convert a column of imported dates

For a small range, a formula is easy to inspect and fill down. Enter the conversion in the first row, then use the fill handle or copy it to the remaining rows. Once you have checked the results, you can copy the converted cells and use Paste Special > Values if you need to replace the original text. Keep a backup column until the dates have been verified.

For a consistent text pattern, Text to Columns can convert a whole selection without leaving formulas. Select the date column, then choose Data > Text to Columns. Choose Delimited and select Next; leave delimiters unchecked and select Next again. Under Column data format, choose Date and the order that matches the source, such as MDY, DMY, or YMD, then select Finish. Inspect the results, especially around dates where both the month and day are 12 or less.

For repeatable imports or larger datasets, Power Query can set the interpretation explicitly. Load the data with Data > From Table/Range, select the date column in Power Query Editor, then choose Data Type > Using Locale and specify the appropriate date type and locale. This is useful when source dates follow a convention different from the computer’s regional settings. After checking the preview, use Close & Load to return the transformed data to Excel.

Common conversion problems and how to fix them

  • The date displays as a number. The value may already be a valid serial. Apply a date format through Home > Number Format rather than running DATEVALUE on it.
  • DATEVALUE returns an error or the wrong day. The text may not match the regional date convention, or it may contain extra characters. Use Text to Columns with the correct date order, Power Query with a chosen locale, or a formula that parses a fixed-position pattern.
  • The formula result is still text. Check whether the conversion function returned a number, then apply a date format. If you used TEXT, the output is deliberately text and changing its number format will not turn it into a date.
  • A date appears as ######. The column may be too narrow to show the selected format. Widen it or choose a shorter date format; the hashes alone do not show that conversion failed.
  • Imported dates sort incorrectly. Text values can sort alphabetically rather than chronologically. Convert them to real date values, then sort again.
  • One day or several hours appear to be missing. Check whether the source includes a time zone, whether the timestamp is in seconds or milliseconds, and whether a system has applied a local-time adjustment.

When the source has multiple formats in one column, avoid applying one parsing rule blindly. Separate the patterns or inspect the exceptions first. A conversion can return plausible-looking dates while silently reversing month and day, so a few visual spot checks are not enough when the dataset is important.

Choose the right conversion method

Use number formatting when Excel already stores a date and you only want a different appearance. Use DATEVALUE for text that Excel can parse using the expected regional convention; use DATE with separate components or a positional formula for predictable input patterns. Text to Columns is convenient for a one-time column conversion, while Power Query suits repeatable imports and explicit locale handling. Use TEXT only when the desired result is intentionally a text string, such as a formatted export label.

Before choosing, consider what happens next. If the converted values will be used for sorting, date subtraction, charts, or filters, keep them as date values. If another system requires a specific text representation, create that output separately and retain the original dates where practical. For imported or shared files, document the intended order—such as DMY—and whether timestamps represent UTC or local time.

Frequently Asked Questions

How do I convert an Excel serial number to a date?
If the number is already an Excel date serial, select the cell and choose a date style from Home > Number Format. The serial is the stored value; formatting makes it readable without changing it.

Why does Excel show a date as a five-digit number?
Excel stores dates as serial numbers. The cell is probably using General or a number format rather than a date format. Apply a date format to display the calendar date.

Can Excel convert a date written as text?
Yes. DATEVALUE can parse text when its format is understood under the current regional settings. For ambiguous or consistently structured input, specify the date order with Text to Columns or parse the components explicitly.

How do I change DD/MM/YYYY to MM/DD/YYYY?
If the cell contains a genuine date, change its display format using Format Cells; the underlying date does not change. If the value is text, first convert it according to its actual day-month-year order, then apply the desired display format.

Does changing the date format convert the date?
No. Formatting changes only how Excel displays a stored value. It does not convert text into a date or change the underlying serial number; use a conversion method for text and formatting for display.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.