Excel: convert text to date and number to date

This tutorial demonstrates various Excel techniques for converting text and numbers into dates, including both formula-based and non-formula methods. You'll learn to efficiently transform text strings into usable date formats.

Often, dates imported from external sources like CSV files appear as text in Excel, hindering proper sorting and formula usage. This tutorial provides multiple solutions to address this common issue.

• Identifying "text dates" in Excel
• Converting numbers to dates in Excel
• Converting text to dates in Excel:
  • DATEVALUE function
  • VALUE function
  • Mathematical operations
  • Handling text strings with custom delimiters
  • "Text to Columns" wizard
  • "Paste Special" method
  • Correcting dates with two-digit years
• Easy text-to-date conversion methods

Distinguishing Between Excel Dates and "Text Dates"

Imported data frequently presents date formatting challenges. Entries might visually resemble dates but are treated as text by Excel, preventing proper sorting and use in formulas, PivotTables, and charts.

Several indicators help distinguish between true dates and text values:

Dates	Text Values
- Right-aligned "Date" format in the Number Format box (Home tab > Number) Status bar displays Average, Count, and SUM when multiple dates selected	- Left-aligned "General" format in the Number Format box Status bar only shows Count when multiple "text dates" selected Leading apostrophe may be visible in the formula bar

Converting Numbers to Dates in Excel

Excel functions converting text to dates return numerical serial numbers. Understanding number-to-date conversion is crucial.

Excel stores dates and times as serial numbers; formatting dictates their display. For example, January 1, 1900 is 1, January 2, 1900 is 2, and January 1, 2015 is 42005. See "Excel date format" for details.

Date functions often return serial numbers. If =TODAY() 7 returns 44286 instead of a date, it's a serial number; simply change the cell format to "Date" (Number Format box on the Home tab). For custom formats, use Ctrl 1 to open the Format Cells dialog, choose "Date," select a format, and click "OK."

Converting 8-Digit Numbers to Dates

Converting 8-digit numbers (e.g., 10032016) requires functions like DATE, RIGHT, LEFT, and MID. A universal formula is impossible due to varying input formats (ddmmyyyy, yyyymmdd, etc.).

The DATE function's arguments are: DATE(year, month, day). Extract year, month, and day from the number and use them as arguments. For example, to convert 10032016 (in A1):

=DATE(RIGHT(A1,4), MID(A1,3,2), LEFT(A1,2))

This extracts the year (last 4 digits), month (middle 2 digits), and day (first 2 digits).

Remember: These formulas work only if all numbers follow the same pattern.

Converting Text to Dates in Excel

Several methods exist for converting text dates to usable Excel dates.

DATEVALUE Function: Converts text dates to serial numbers. =DATEVALUE(A1) (where A1 contains the text date). Remember to format the cell as "Date" afterward.

VALUE Function: More versatile than DATEVALUE; handles dates and times. =VALUE(A1).

Mathematical Operations: Simple operations like =A1 0, =A1*1, =A1/1, or =--A1 can force text-to-number conversion. Format the result as "Date."

Text Strings with Custom Delimiters: Use "Find and Replace" (Ctrl H) to replace delimiters with slashes (/) before using DATEVALUE or VALUE. Alternatively, use the SUBSTITUTE function: =VALUE(SUBSTITUTE(A1, ".", "/")) (replaces "." with "/").

"Text to Columns" Wizard: A non-formula approach for simple and complex text dates. For simple dates, select the column, go to Data > Text to Columns, choose Delimited, select the delimiter, and choose "Date" in the final step. For complex dates, split the text into columns using delimiters, then use the DATE and MONTH functions to reconstruct the date.

"Paste Special" Method: A quick method for simple text dates. Copy an empty cell, select the text dates, right-click, choose "Paste Special," and select "Add."

Fixing Two-Digit Years: Excel's error checking often flags two-digit years. Select the cells, click the error indicator, and choose to convert to 19XX or 20XX. Ensure error checking is enabled in Excel Options > Formulas > Error Checking.

Easy Text-to-Date Conversion (using a third-party tool)

Consider using a dedicated tool like Ablebits' Text to Date for simplified conversion. This tool offers a user-friendly interface for selecting date formats and handling various scenarios.

This tutorial provides a comprehensive guide to converting text and numbers to dates in Excel. Choose the method best suited to your data and preferences.

The above is the detailed content of Excel: convert text to date and number to date. For more information, please follow other related articles on the PHP Chinese website!