Dates in Excel are displayed as dates, but in reality, they are numbers formatted in a way that is easily recognizable by people.
For example, this is the value returned by the NOW function.
9/20/18 2:33 PM
It may look different on your worksheet depending on the format system you have set on your computer.
But if you highlight this cell, you can right-click and choose Format cells, you can see that there is a day, month, year, hour and minute. But it’s not all the information that is stored inside this cell.
You can also insert seconds into the Type field and the seconds will appear inside the Sample area. Click ok to format this cell in such a way. Now if you enter the NOW function once more into this cell, it will remember the new formatting.
Now, the formatting looks like this.
Convert date to text and keep the format
In order to convert a date and keep the format, you can’t just choose Home >> Number >> Text because it’s going to be formatted into a number: 43363.60656.
You can learn more about storing dates and times in excel in this article.
In order to format a date to text, you have to use the TEXT function. It has two parameters.
Let’s say that you want to have the following format.
9/20/18 2:33:27 PM
What we need to do is to right-click this cell and choose Format Cells. Copy Type and insert it as the second parameter of the Text function.
=TEXT(A1,"[$-en-US]m/d/yy h:mm:ss AM/PM;@")
This cell is not text yet, it’s a formula. You can copy this row and paste it as values. Now, if you choose Home >> Number >> Text the text will be formatted in a proper way.
The simple trick to treat the value as the text is to use “‘” character in the front of a value.
‘9/20/2018 2:33:27 PM
It will be displayed as 9/20/2018 2:33:27 PM.
Now, you can convert it to text without using the TEXT function.