There is no straightforward way to enter a string of text in Excel along with a date or time. Excel simply does not allow you to combine text and dates or text and time together in the same cell. However, there is a simple workaround that makes it possible for you to do this. In this Excel tutorial, we show you how to combine text with a date or time using a simple formula tweak.
How to Combine Text with a Date in Excel
Let’s take an example scenario where I have a string of text in cell A1 called Stock Availability On: In cell B1, I want to enter the current date. And in cell C1, I want to enter the string of text that should read like this Stock Availability On: XXX (the current day’s date). Here’s how to do this:
1. In cell B1, enter the TODAY function to incorporate today’s date. Type=TODAY() and press Enter. If necessary, format the date according to your preference. You can do that by selecting the Home tab and in the Number group, from the Number drop-down list, select a short date. Or go to More Number Formats and in the Number Format dialog box, select a date format of your choice.
2. To combine the contents of both cells, we use the & symbol. However, in cell C3, if we type =A1&B1, we will get something like this.
Stock Availability On:42668
That’s because Excel treats both the cell values as text. We need to tell Excel to express the date in the proper format, i.e the date format. For this we use the TEXT function. The Text function uses 2 arguments. The first argument is a value, such as a date or a number, which is converted to a text value. The second argument is format_text, which converts the text value in a format that you want. So the text function will read like this:
3. Now we need to combine both the functions, which should read:
That’s It! You just used the Today and TEXT functions to combine a string of text with a date in Excel.
How to Combine Text with Time in Excel
Just like how you combined a string of text with a date, you can also combine a string of text with time in Excel. Here you’ll use 2 functions to accomplish the task. One is the Now function, which indicates the current time and the other one is the TEXT function, which allows you to display the time in its proper format.
Let’s say we have some text in cell A3 that reads The Time Now is: In cell B3, we want to display the current time and in cell C3, we want to combine the text and time to read The Time Now is: XXX (the current time). To do this:
1. In cell B3, we enter the time function. Type =(NOW(). Again, if necessary, format the time according to your preference. You can do that by selecting the Home tab and in the Number group, from the Number drop-down list, select a time format. Or go to More Number Formats and in the Number Format dialog box, select a time format of your choice.
2. In cell C3, we enter the combination of the text in cell A3 and the TEXT function, which is TEXT(B3,”hh:mm”). So the function will read:
That’s it! You just learned how to combine text with a date or time in Excel and should have no trouble next time you want to do the same.
If you found this tutorial useful, be sure to learn more good stuff in our Learn Excel series. If you want to take up an introductory course on Excel, consider taking up our Udemy course, Excel 2016 for Beginners. It is available at a nominal price of just $10. As, always, your feedback is much appreciated.
Leave a Reply