Stop Excel from Converting Date to Number in Formula (2 Ways)

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 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.

Download Practice Workbook

You can download the practice workbook from here:

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

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.

• For this, use the following formula in cell E5:

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

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

Read More: Text Won’t Convert to Date in Excel (4 Problems & Solutions)

Similar Readings

• How to Convert Unix Timestamp to Date in Excel (3 Methods)
• Convert Active Directory Timestamp to Date in Excel (4 Methods)
• How to Disable Auto Convert to Date in Excel (2 Methods)
• Convert SAP Timestamp to Date in Excel (4 Ways)
• How to Convert Week Number to Date in Excel (2 Suitable Methods)

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.

• Here, in cell 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.

• So, 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.

• Now, 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 there an apostrophe will be added automatically before the space. That means the date values are in Text format now.

Read More: How to Convert General Format to Date in Excel (7 Methods)

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. Also, 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 Convert Number to Date in Excel (6 Easy Ways)

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, ExcelDemy, 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 13 Digit Timestamp to Date Time in Excel (3 Ways)
• Excel VBA to Convert Date and Time to Date Only
• How to Convert Date to Julian Date in Excel (3 Easy Ways)
• How to Convert Text Date and Time to Date Format in Excel (7 Easy Ways)
• Convert Serial Number to Date in Excel (7 Easy Ways)