Look Who it Was Random video

Excel - Concatenating Text and a Date in Excel Requires the TEXT function - Episode 414

495 views · Published 29 September 2009 · 2:16 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Concatenating Text and a Date in Excel Requires the TEXT function.
Welcome back to another episode of the MrExcel netcast! In today's video, we're going to dive into the world of joining dates in Excel. In our previous episode, we learned how to use the concatenation character to join text from different columns. However, things get a little tricky when we try to join text and dates together. But don't worry, I've got a solution for you.

Let's say we have a list of names, birth dates, and a phrase we want to join them with. Using the concatenation character, we can easily combine the text from columns A and B with the phrase "was born on" to get a sentence like "Toya was born on January 18th, 1958". But instead, we end up with a random number like 21203. What's going on here? Well, that number represents the number of days since January 1st, 1900. Interesting, but not exactly what we're looking for.

To fix this issue, we need to use the text function. By specifying the custom number format, we can convert that random number into the date format we want. In this case, we use "mm/dd/yyyy" to get the desired result of January 18th, 1958. If you're familiar with custom number format codes, you can use any code you want to format the date. But if not, just stick with the basic "mm/dd/yyyy" format.

And there you have it! Now you know how to join dates and text in Excel without any random numbers getting in the way. This is a useful skill to have, especially when working with dates or currency in your data. So next time you need to combine text and dates, remember to use the text function and specify the custom number format. Thanks for watching and be sure to tune in for our next episode of 2007 Thursday!"

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

Table of Contents:
(00:00) Introduction to concatenation character
(00:22) Problem with joining text and dates
(00:39) Explanation of incorrect result
(00:49) Use of text function to format cell as text
(01:07) Example of custom number format code
(01:41) Importance of using text function for dates and currency
(01:56) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Concatenating text and numbers in Excel
Converting dates to a specific format in Excel
Custom number format codes in Excel
Excel tutorial on working with dates and text
Formatting cells as text in Excel
How to join text from different columns in Excel
Importance of using the text function when joining text and dates in Excel
Problem with joining text and dates in Excel
Tips for joining text and dates in Excel
Understanding the number of days since January 1st, 1900 in Excel
Using the text function to format dates as text in Excel
YouTube video tutorial on using the concatenation character in Excel

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152648/



If you try to use the concatenation character to join text with a date, you will not get the results that you expected. Episode 414 shows you how to modify the formula to properly format the date.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

More from this channel