Although we don’t recommend it, occasionally you may want to convert a date in Excel to be seen as words instead of a valid Excel date. Perhaps you want to convert the month number into the month’s name. There are 2 options to do this and which is best depends on what you want to do with the resultant cells.
FORMAT the date to show the month name
In this case you are NOT changing the underlying data. Excel still sees the original date and all it does is format it in such a way that you only see the month name (or the day of the week, year etc).
So below in column A we have the date. In column B (e.g. cell B5) the formula bar shows the same information of the full date, but the cell only shows the month name.
This is a formatting trick. You can achieve it by formatting any valid Excel date into the custom format of ‘mmmm’ as shown below (use ‘mmm’ for the shortened name).
It is important to realise that this is only formatting. Excel still sees the date as a valid date. So for example if you tried to build an IF onto this cell and checked if it was the word ‘September’ it would give a FALSE as that cell is still 2019/09/30 .
Use a Function to change month name into a word
In order to make the month name a word which we can work with (as per the IF example above), we need to use the TEXT function.
The TEXT function tells Excel to take, typically some form of number (remember dates are numbers) and convert it into a text cell (even if it is a number it will appear with “”).
So below in column C we have used the TEXT function to look at the date in column A, and convert it to text using the format ‘mmmm’ (the same as we used in the format cells- useful to remember that you can look in the format cells dialogue box to see what codes give what formats).
Now if you had to build an IF looking at this cell, the IF would recognise the word ‘September’ and react correctly.
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.