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:
🔎 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.
- 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
- 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.
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.
- Add an apostrophe or space before inserting values in the cell.
Download Practice Workbook
You can download the practice workbook from here:
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.
- 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