How to convert number string to date and time in Excel?
In daily Excel operations, it is common to encounter situations where dates or times are entered as continuous number strings such as "20110312" to expedite data input. While this direct method can enhance speed and efficiency for quick recording, these numeric strings are not easily recognized as dates by Excel and may cause confusion or errors in later calculations, formatting, or data analysis. Therefore, for more formal data use or consistent workbooks, you may need to convert such number strings into Excel's standard, recognized date or date-and-time formats.
Below, you will find practical step-by-step solutions to efficiently convert number strings into standard date and/or time formats in Excel, addressing both simple and complex cases with and without auxiliary tools:
Convert number string such as 20110312 to standard date formatting
Convert number string like 40614.43 to date and time formatting
Convert number string 20110312 to date formatting with Text to Column
When working with datasets imported from other systems or manually entered, you may find that dates are stored as continuous number strings (for example, "20110312"), which are not automatically recognized as dates in Excel. The Text to Columns feature is a straightforward way to convert these numerical strings into date format, as long as the sequence of digits matches a known pattern such as YYYYMMDD.
1. Select the number strings you want to convert to dates, and click Data > Text to Columns on the Ribbon. This tool is suitable for handling column data and can process multiple cells at once.
2. In the Text to Column dialog, simply click Next twice to skip to Step 3 of 3. In this step, check Date, then choose YMD from the list (this matches the year-month-day order of "20110312").
3. Click Finish. The selected cells will convert instantly to standard date formatting. 
If the conversion result is not as expected, verify the original string structure and selected date sequence. Also, make sure cells do not have text formatting applied, which may prevent conversion.
Convert number string to standard date with Kutools for Excel
When you have a large range of number strings from different sources, or the number string patterns are complex and inconsistent, using the built-in tools could be laborious or may not correctly identify all date types. In such cases, the Kutools for Excel's Convert to Date feature provides a convenient and efficient solution, supporting a wider range of nonstandard date patterns and batch operations.
After free installing Kutools for Excel, please do as below:
Select the date strings or numbers you want to convert to standard dates, then click Kutools > Content > Convert to Date from the menu.
The selected number strings will immediately be transformed into Excel's date format, which you can further configure or use for calculations.
This solution is particularly advantageous when handling mixed or complex date formats, reducing the need for separate step-by-step operations and avoiding mistakes due to inconsistent source data. If you notice that some items remain as text, confirm they do not contain hidden characters or unexpected separators.
Excel Formula – Convert number string like 20110312 to date
If you prefer a formula-based approach, or need to automate the conversion for a specific structure such as YYYYMMDD (e.g., "20110312"), you can use a combination of DATE, LEFT, MID, and RIGHT functions. This method is suitable for situations where you want to keep original data unchanged and display the date conversion in a new column.
1. In a new blank cell (for example, B1) next to the number string (in cell A1), input the following formula:
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))2. Press Enter to confirm. If you have multiple rows to convert, drag the fill handle down to apply the formula to other cells. This will convert each number string to a correctly formatted date.
If your data is in a format other than YYYYMMDD, adjust the formula accordingly (for example, swap the LEFT, MID, and RIGHT positions for your date order). Double-check that your number string is exactly 8 digits and contains only numeric characters to avoid formula errors.
Convert number string 40614.43 to date and time with Format Cells
Sometimes, dates and times are stored as serial numbers with decimal values (for example, "40614.43"). In Excel, the integer part represents the date and the decimal part represents the time. To properly display both date and time within the same cell, you can customize the cell format using the Format Cells feature.
1. Select the number or range you want to convert and display as date and time, then press Ctrl +1 (or right-click and choose Format Cells) to open the dialog. On the Number tab, select Custom from the category list, then enter mm/dd/yyyy hh:mm in the Type box.
You can adjust the display format as desired (for example, add seconds by using "mm/dd/yyyy hh:mm:ss"). 
2. Click OK. The cell(s) will now show readable date and time information instead of an unformatted number.
If the result shows "#####" or unexpected values, ensure your cell is wide enough and does not have conflicting formatting.
Excel Formula – Extract date and time separately from date serial with decimals
If you have Excel date serials that include decimal fractions (like "40614.43"), the integer part is the date, and the decimal part is the time. You can separate these into two readable values using formulas—useful when importing raw data, log files, or automated reports.
To extract the date:
1. Enter the following formula in a blank cell (e.g., B1) if your original value is in A1:
=INT(A1)2. Format this cell as a date: Right-click, select Format Cells, choose Date, and pick your preferred date format.
To extract the time:
1. Enter this formula in the next cell (e.g., C1):
=A1-INT(A1)2. Format this cell as time: Right-click, choose Format Cells, select Time, and set a format such as "hh:mm:ss".
This way, you can quickly display the respective date and time elements separately. Make sure your original number is a valid Excel serial number with a decimal to use this method.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in