How to Convert Text to Date and Number to Date in Microsoft Excel?

Microsoft Excel is an extensively used application for data management. It allows users to perform various functions to make data analysis easy and efficient. One of the most commonly used functions in Excel is converting text and number to date formats. In this article, we will explain how to convert text to date and number to date in Microsoft Excel.

Converting Text to Date in Microsoft Excel

Converting text to date is a straightforward process in Excel. The main idea is to transform the text string in a format recognizable as a date value.

We can use the following formula to convert text to date format:

=DATEVALUE(A1)

Where A1 is the cell reference containing the text value.

Let’s see an example. Suppose we have a list of dates in text format and we want to convert them into a recognizable date format.

To convert text to date, follow the steps mentioned below:

Step 1: Add a new column beside the column containing dates in text format.

Step 2: Enter the following formula in the first row of the new column and press enter:

=DATEVALUE(A2)

Note that we use A2 as the first row contains column headers, which are non-date values.

Step 3: Drag the formula to copy it to all the rows.

As you can see, the text values are now recognizable as date values.

Converting Number to Date in Microsoft Excel

Converting numbers to date is another commonly used function in Excel. We can use the following formula to convert numbers to date format:

=DATE(YEAR,MONTH,DAY)

Where YEAR, MONTH, and DAY are the references of the cells containing the numerical values.

Let’s see an example. Suppose we have a column of dates where the month and the day are written in separate columns, and we want to combine them into a single date column.

To convert numbers to date, you need to follow these steps:

Step 1: Add a new column beside the column containing the dates in separate columns.

Step 2: Enter the following formula in the first row of the new column:

=DATE(A2,B2,C2)

Where A, B, and C are the columns that contain the year, month, and day values, respectively.

Step 3: Drag the formula to copy it to all the rows.

As you can see in the example, the year, month, and day values are combined to form a date column.

Conclusion

Converting text and number to date format in Excel is a primary function that every user should know. This article has shown you how to convert text and number to date in Microsoft Excel using simple and easy-to-follow steps. You can use these formulae to convert your data into a recognizable and usable format, making your data analysis process easier and effective.