How to Stop Excel from Converting Date to Number in Formula

Get FREE Advanced Excel Exercises with Solutions!

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.

 Excel is Converting Date to Number in Formula


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:

=CONCAT(TEXT(D5,"DD/MM/YYYY"),"_",B5,"_",C5)

🔎 Formula Breakdown:

  • TEXT(D5,”DD/MM/YYYY”) : Here, the TEXT function converts the date to a text and DD denotes format for day value; similarly MM for the month and YYYY for the year.
  • CONCAT(“02/01/2022″,”_”,B5,”_”,C5) : Then, the CONCAT function combines the selected cells and the date value will remain in the same format.

Using TEXT Function to Stop Excel from Converting Date to Number in Formula

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

Dragging fill handle

  • 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


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.

Adding Apostrophe/Space Before Dates to Stop Excel from Converting Date to Number in Formula

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

converting cells to text format to Stop Excel from Converting Number to Date

Or,

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

Add an apostrophe or space before dates

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

Osman Goni Ridwan
Osman Goni Ridwan

I am Ridwan, graduated from Naval Architecture and Marine Engineering Dept, BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands. My prime goal is to be a data analyst as I do love to solve problems and play with data.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo