Converting Excel Dates to Text


« Back to videos

Additional Information:

Dates are stored in Excel as numbers known as date serials. This enables you to perform basic arithmetic such as calculating tomorrow's date simply by finding today's date and adding 1. If dates were stored as text, it would not be at all obvious that the 1st February is preceded by the 31st January. Storing dates as numbers also means they can be re-formatted.

However, this means the text displayed in a cell is merely the formatting that has been applied. When you attempt to use a date "as written", you need to ensure the output cell is formatted the same way. If, as in this video, you attempt to use text functions on your date, it will behave very strangely.

These problems can be solved by using the TEXT function. This takes the form "TEXT(value)" and converts any value in any cell, into the string of letters you see on screen.