{"id":3797,"date":"2023-02-03T16:20:13","date_gmt":"2023-02-03T16:20:13","guid":{"rendered":"https:\/\/www.bizinfograph.com\/resource\/?p=3797"},"modified":"2024-03-13T19:39:52","modified_gmt":"2024-03-13T19:39:52","slug":"how-to-convert-serial-number-to-date-in-excel","status":"publish","type":"post","link":"https:\/\/www.bizinfograph.com\/resource\/how-to-convert-serial-number-to-date-in-excel\/","title":{"rendered":"How to Convert Serial Number to Date and Text to Date in Excel?"},"content":{"rendered":"

Convert Serial Number to Date and Text to Date in Excel to enhance your data’s clarity and utility. This crucial process allows for precise time-based analysis, seamless data integration, and effective planning and scheduling. By mastering these conversion techniques, you can ensure that your Excel spreadsheets are both accurate and user-friendly, making your data more actionable and your insights more reliable. Leverage the power of Excel to transform your serial numbers and text into meaningful dates, unlocking the full potential of your data for better decision-making and organization.<\/p>\n

This Content Covers: <\/strong><\/p>\n

    \n
  1. How to <\/strong>Convert Serial Numbers to Dates<\/strong> in Excel?<\/strong><\/a><\/li>\n<\/ol>\n

    1.1 <\/strong>Using the In-Built Date Format Options in the Ribbon<\/strong><\/p>\n

    1.2 Using the Format Cells function<\/strong><\/p>\n

    1.3 <\/strong>Using TEXT Formula<\/strong><\/p>\n

      \n
    1. How to Convert Text to Date in Excel?<\/strong><\/a><\/li>\n<\/ol>\n

      2.1 Using the DATEVALUE function<\/strong><\/p>\n

      2.2 Using the VALUE function<\/strong><\/p>\n

      2.3 Using Text to Column Wizard<\/strong><\/p>\n

      2.4 Using Paste Special<\/strong><\/p>\n

      1. How to <\/strong>Convert Serial Numbers to Dates<\/strong> in Excel?<\/strong><\/h2>\n

      1.1 <\/strong>Using the In-Built Date Format Options in the Ribbon<\/strong><\/h3>\n

      \"Convert<\/p>\n

      Suppose you have this list of serial numbers, now to convert them into dates use the following steps.<\/p>\n

      Step 1:<\/strong> Select the cells that contains these numbers and go to Home>>General.<\/strong><\/p>\n

      \"Convert<\/p>\n

      Step 2: <\/strong>From the drop-down menu, select Short Date<\/strong> or Long Date.<\/strong><\/p>\n

      \"Convert<\/p>\n

      Step 3: Long Date<\/strong> have been selected here so the numbers changed into long dates.<\/p>\n

      \"Convert<\/p>\n

      1.2 Using Format Cells function<\/strong><\/h3>\n

      Step 1: <\/strong>Select the cells and right click. Choose Format Cells.<\/strong><\/p>\n

      \"Convert<\/p>\n

      Step 2: <\/strong>In the Format Cells <\/strong>dialogue box go to Number>>Date<\/strong> and select any date format you like then click OK.<\/strong><\/p>\n

      \"Convert<\/p>\n

      Step 3: <\/strong>The selected Excel numbers have been converted into dates.<\/p>\n

      \"Convert<\/p>\n

      1.3 <\/strong>Using TEXT Formula<\/strong><\/h3>\n

      Step 1: <\/strong>Select cell B2 and insert the TEXT formula given below. You can choose any date format you like inside (\u201c\u201d).<\/strong><\/p>\n

      =TEXT(A2,”d\/m\/yyyy”)<\/strong><\/p>\n

      \"Convert<\/p>\n

      Step 2: <\/strong>Click and drag the cell downwards to apply the formula in the other cells of this column and all the serial numbers in Column A will be converted into dates in column B.<\/p>\n

      \"Convert<\/p>\n

      2. How to Convert Text to Date in Excel?<\/strong><\/h2>\n

      2.1 Using DATEVALUE function<\/strong><\/h3>\n

      \"Convert<\/p>\n

      DATEVALUE <\/strong>function only works on values that are formatted as text. All the data in Text String items look to be like dates but they are formatted as texts. When you click on any of them you will see Text written inside the box in ribbon. To convert these kind of text values into dates, follow the steps below.<\/p>\n

      \u00a0<\/strong>Step 1: <\/strong>Insert this formula in cell B2 and click Enter<\/strong>. Drag all the cells downwards to apply the formula in all these cells as well.<\/p>\n

      \"Convert<\/p>\n

      Step 2:<\/strong> The text stings values are converted into serial numbers. Now select the range and convert them into date from the drop-down menu of the ribbon or use any other methods that are shown above of converting serial numbers into dates. Long Date<\/strong> format is used in the below given picture.<\/p>\n

      \"Convert<\/p>\n

      2.2 Using VALUE function<\/strong><\/h3>\n

      Unlike DATEVALUE<\/strong> function you can convert any kind of text strings or number values that looks like dates into dates by using the VALUE<\/strong> function.<\/p>\n

      Step 1: <\/strong>Select cell B2 and insert the formula from the picture and hit Enter<\/strong> key.<\/p>\n

      \"Convert<\/p>\n

      Step 2:<\/strong> Drag the cell downwards to apply the formula in all the other cells.<\/p>\n

      \"Convert<\/p>\n

      Step 3: <\/strong>Select all the serial numbers and convert them into dates using any of the previous methods shown above.<\/p>\n

      \"Convert<\/p>\n

      2.3 Using Text to Column Wizard<\/strong><\/h3>\n

      Now we will show you how you can convert text strings into dates using Text to Column<\/strong> wizard for this text string given below.<\/p>\n

      \"Convert<\/p>\n

      Step 1: <\/strong>Select the range from Text Strings column and go to Data>>Text to Columns.<\/strong><\/p>\n

      \"Convert<\/p>\n

      Step 2: <\/strong>From the dialogue box select Delimited<\/strong> and press Next.<\/strong><\/p>\n

      \"Convert<\/p>\n

      Step 3: <\/strong>Uncheck all the boxes>>click Next.<\/p>\n

      \"Convert<\/p>\n

      Step 4: <\/strong>Select Date>>choose a date format<\/strong> and click on Finish.<\/strong><\/p>\n

      \"Convert<\/p>\n

      Step 5: <\/strong>All the text strings have been converted into dates.<\/p>\n

      \"Convert<\/p>\n

      2.4 Using Paste Special<\/strong><\/h3>\n

      Step 1: <\/strong>Copy and empty cell using CTRL+C<\/strong> command. Select the range of cells you want to convert into dates, right click and select Paste Special.<\/strong><\/p>\n

      \"Convert<\/p>\n

      Step 2: <\/strong>From Paste Special<\/strong> dialogue box select Add <\/strong>and click Ok.<\/strong><\/p>\n

      \"Convert<\/p>\n

      Step 3: <\/strong>The text strings are turned into serial numbers. Select the range.<\/p>\n

      \"Convert<\/p>\n

      Step 4: <\/strong>Using the ribbon or any other of the above- noted techniques, convert the serial numbers into dates.<\/p>\n

      \"Convert<\/p>\n

      Application of Convert Serial Number to Date and Text to Date in Excel<\/strong><\/h2>\n