How to Stop Excel from Converting Date to Number in Formula

You have come to the right place if you are looking for the answer or some unique tips to stop Excel from converting date to number in a formula. There are certain ways to stop Excel from converting date to number in formula. This article will walk you through each and every step with appropriate examples. As a result, you can use them easily for your purpose. Let’s move on to the article’s main discussion.

How to Stop Excel from Converting Date to Number in Formula: 2 Ways

Here, I have a dataset of first names, last names, and joining dates. I want to create an ID by combining the columns but while using the CONCAT function for date values, dates are being converted to numbers.

In this section, I will show you 2 quick and easy methods to stop Excel from converting date to number in formulas on the Windows operating system. This article contains detailed explanations with clear illustrations for everything. I have used the Microsoft 365 version here. However, you may use any other version depending on your availability. Please leave a comment if any part of this article does not work in your version.

1. Using TEXT Function

Before using in CONCAT function, you have to convert the dates to text format, so the formula won’t convert them to numerical values.

• Use the following formula in cell E5:

• Now, drag the Fill Handle icon to paste the used formula to the other cells of the column.

• Thus, you can stop Excel from converting date to number in the formula.

Read More: How to Convert Date to Number in Excel

Similar Readings

• How to Convert Date to Text YYYYMMDD
• How to Convert Date to Text Month in Excel
• How to Convert Date to Month in Excel
• How to Convert Date to Year in Excel

2. Adding Apostrophe/Space Before Dates

There is another way to stop Excel from converting date to number in the formula and this is to add an apostrophe before the numbers in cells.

• In range E5:E10, the CONCAT function is used to combine cells in the left 3 columns. But, in the formula, the dates are converted to numbers.

• Here I have added a simple apostrophe before the date value in cell D5 and the formula is now giving the output keeping the date as it is.

• Similarly, you have to add apostrophes before each cell of the date column and thus the formula will work perfectly keeping the date formats.

Adding Space Before Dates:

Also as an alternative to using apostrophes, you can use space before the dates to keep them as it is in the formula. When you add a space before dates and press Enter, you will see that an apostrophe will be added automatically before the space. That means the date values are in Text format.

Read More: How to Convert 7 Digit Julian Date to Calendar Date in Excel

How to Stop Excel from Converting Numbers to Date

Sometimes when a user inserts numbers with hyphens or slashes delimiters for any purpose, Excel converts them to date. To avoid this problem you have two ways:

• Select all of the cells and set their format as TEXT. Then, insert the values.

Or,

• Add an apostrophe or space before inserting values in the cell.

Read More: How to Use Formula to Change Date Format in Excel

Download Practice Workbook

You can download the practice workbook from here:

Conclusion

In this article, you have found how to stop Excel from converting date to number in the formula. I hope you found this article helpful. You can visit our website to get more Excel-related content. Please leave comments, suggestions, or queries if you have any in the comment section below.

Related Articles

• How to Convert Date to Quarter and Year in Excel
• How to Convert Date to Month and Year in Excel
• How to Convert Date to Week Number of Month in Excel
• How to Convert Date to Day of Week in Excel
• How to Convert Date to Day of Year in Excel
• How to Convert Date to Julian Date in Excel
• How to Remove Year from Date in Excel