We’ve all been there, when you use Excel and you want to include a date and you can’t get it to read as the other dates, or format it in a way that it reads the date as text, and not a series of numbers. It’s frustrating, isn’t it? Sometime we just need a bit of Excel support. However, it doesn’t have to be, because there is a simple solution. The date can be converted into text format very simply, using what’s known as the TEXT function.
The TEXT function removes the original formatting of the date from the cell, and instead allows this to be used as a text string. This means instead of seeing the date in the cell as 01/01/2012, it will instead read like, Jan 1; however this depends on how you want the text to be displayed, which we will get onto later.
So how do you do it? Simply fire up your spread sheet with the dates that you want to convert. Choose a column next to these dates, this will be the column where you convert the dates into text. You may even want to include a title in the top cell, just categorise the column and remind yourself of its purpose. In the first cell beneath the title, type “=TEXT”. As mentioned, this is the text conversion function, which effectively tells Excel to reformat whatever value is in the cell into a text string. It’s important to remember to include brackets after the word TEXT, otherwise it won’t convert properly.
Within the brackets, simply select the cell with the original formatted date contained within it, for example A2. So far, our cell will look as follows, “=TEXT(A2)”. However, you need to add how you would like the text date to be formatted. So, after the value A2, you need to add a comma (still within the brackets) and then the format of the text. It’s entirely up to you how you want your date to read as text. For example you could have the day, month and year, which would look like “dd-mm-yyyy”. Alternatively, you could have something a lot cleaner and simpler like “mmm-dd”. Either way, it’s your choice.
So, now your cell should look like something along the lines of =TEXT(A2, “mmm-dd”). This function will convert the date in cell A2 from 01/01/2012 into Jan 01. That’s pretty much the cut and thrust of it. However, if you want to combine dates within the same cell, or combine other data, you can also use the function =CONCATENATE.
So, in another column, simply select a cell and write the aforementioned command. After the command, open up a bracket and select the cells that you want to appear the same cell together. When you do, Excel automatically places a comma between the two fields. However, they won’t appear separated in you new cell, but rather just next to each other, which can look a little messy. So, you’ll want to add a space between these two fields. This can be achieved by adding open quotes in between the two fields that you want to combine.
The problem with doing this with dates is that Excel doesn’t read date values, but instead reformats it into something that doesn’t mean anything to anyone. This is where converting the date to TEXT will work wonders. So, if you’re trying to combine dates together in the same cell, or are looking to include a date next to another value or field, use the TEXT function within the brackets after the =CONCATENATE function. If you’re just looking to convert the date alone, then just use the =TEXT function.