130

In a cell in Excel sheet I have a Date value like:

01/01/2010 14:30:00

I want to convert that Date to Text and also want the Text to look exactly like Date. So a Date value of 01/01/2010 14:30:00 should look like 01/01/2010 14:30:00 but internally it should be Text.

How can I do that in Excel?

brettdj
  • 54,857
  • 16
  • 114
  • 177
Chaitanya MSV
  • 6,706
  • 12
  • 40
  • 46
  • 6
    Excel may not be a full-blown IDE, but if you're given data in Excel and need results in Excel, some VBA programming makes all kinds of sense. – brichins Dec 03 '11 at 00:02

10 Answers10

246
=TEXT(A1,"DD/MM/YYYY hh:mm:ss")

(24 hour time)

=TEXT(A1,"DD/MM/YYYY hh:mm:ss AM/PM")

(standard time)

JohnB
  • 18,046
  • 16
  • 98
  • 110
Kirill
  • 2,590
  • 1
  • 17
  • 16
  • 3
    Full reference to the TEXT function especially to know how to set formats: see Guidelines for date and time formats on this page http://office.microsoft.com/en-us/excel-help/text-function-HP010062580.aspx – codea Sep 09 '13 at 21:07
  • When i try to do this conversion, there is a "#AD?" text and it says something's wrong. – AloneInTheDark Mar 31 '14 at 13:08
  • 1
    @AloneInTheDark When you follow the link from codea, the formats work but only if you have a windows OS that uses the English formats. Mine uses the Dutch formats which means that for example "DD/MM/YYYY hh:mm:ss" becomes "DD/MM/JJJJ uu:mm:ss" because in Dutch, 'year' is 'jaar' and 'hour' is 'uur' i.e. other starting letters (while 'day', 'month', 'minutes' and 'seconds' start with the same letters in both Dutch and English). Basically, translate the starting letters of the formats to the language of your OS. – Sem Vanmeenen Apr 17 '14 at 12:16
  • 3
    And the opposite conversion is done with `DATEVALUE("01/01/2010")` – Zenadix Sep 11 '14 at 19:06
  • Any way to use the format I'm already using for Date display without having to specify it? – palswim Sep 25 '16 at 05:39
  • In spanish format change `yyyy` with `aaaa` and use `TEXTO`: `=TEXTO(B5;"dd/mm/aaaa")` (this may help other users) – Dani Apr 27 '18 at 06:16
10

Here is a VBA approach:

Sub change()
    toText Sheets(1).Range("A1:F20")
End Sub

Sub toText(target As Range)
Dim cell As Range
    For Each cell In target
        cell.Value = cell.Text
        cell.NumberFormat = "@"
    Next cell
End Sub

If you are looking for a solution without programming, the Question should be moved to SuperUser.

marg
  • 2,817
  • 1
  • 31
  • 34
7

Here's another option. Use Excel's built in 'Text to Columns' wizard. It's found under the Data tab in Excel 2007.

If you have one column selected, the defaults for file type and delimiters should work, then it prompts you to change the data format of the column. Choosing text forces it to text format, to make sure that it's not stored as a date.

Adam Miller
  • 767
  • 1
  • 9
  • 22
4

In some contexts using a ' character beforehand will work, but if you save to CSV and load again this is impossible.

'01/01/2010 14:30:00
Nick Fortescue
  • 43,045
  • 26
  • 106
  • 134
4

Couldnt get the TEXT() formula to work

Easiest solution was to copy paste into Notepad and back into Excel with the column set to Text before pasting back

Or you can do the same with a formula like this

=DAY(A2)&"/"&MONTH(A2)&"/"&YEAR(A2)& " "&HOUR(B2)&":"&MINUTE(B2)&":"&SECOND(B2)

Shankar ARUL
  • 12,642
  • 11
  • 68
  • 69
  • 1
    Upvoted, because to my understanding, this is the only working method that is not subject to language settings. If you intent to share your work, using TEXT() will bring you trouble. Even within one country (e.g. Germany), you will find people with German and English configurations. – Dr. V Jan 11 '19 at 09:00
  • 1
    big thanks and upvote! It's working for me, because i can't to use =TEXT (python library formulas) – aveLestat Nov 27 '19 at 16:39
2

I have no idea about the year of publication of the question; it might be old now. So, I expect my answer to be more of a reference for future similar questions after my post.

I don't know if anybody out there has already given an answer similar to the one I am about to give, which might result -I think- being the simplest, most direct and most effective: If someone has already given it, I apologize, but I haven't seen it. Here, my answer using CStr instead of TEXT:

Asuming cell A1 contains a date, and using VBA code:

Dim strDate As String

'Convert to string the value contained in A1 (a date)
strDate = CStr([A1].Value)

You can, thereafter, manipulate it as any ordinary string using string functions (MID, LEFT, RIGHT, LEN, CONCATENATE (&), etc.)

Community
  • 1
  • 1
Prof. Juan Manuel Alonso D.
  • 67
  • 7
1

If you are not using programming then do the following (1) select the column (2) right click and select Format Cells (3) Select "Custom" (4) Just Under "Type:" type dd/mm/yyyy hh:mm:ss

Harihara Iyer
  • 619
  • 1
  • 5
  • 5
1

In Excel 2010, marg's answer only worked for some of the data I had in my spreadsheet (it was imported). The following solution worked on all data.

Sub change()
    toText Selection
End Sub

Sub toText(target As range)
Dim cell As range
Dim txt As String
    For Each cell In target
        txt = cell.text
        cell.NumberFormat = "@"
        cell.Value2 = txt
    Next cell
End Sub
Community
  • 1
  • 1
Archimaredes
  • 1,397
  • 12
  • 26
0

As Text is localized it will break when trying you try to share your files over diffrent cultures. ÅÅÅÅ-MM-DD might work perfectly in sweden, is US, Germany or israel it will turn to shit. The reasonable solution would be that english was accepted everywhere, but it's not.

Basically DON'T EVER use text as intended to format dates. Here is how to create the date in ISO format. TEXT is used to ensure leading

=YEAR(A1)&"-"&TEXT(MONTH(A1);"00")&"-"&TEXT(DAY(A1);"00")

If you want it backwards, sideways or whatever, just change it. https://www.reddit.com/r/ISO8601/comments/enhlp6/logic_of_the_different_date_time_systems_with/

Griffin
  • 785
  • 5
  • 13
-1

The selected answer did not work for me as Excel was still not converting the text to date. Here is my solution.

Say that in the first column, A, you have data of the type 2016/03/25 21:20:00 but is stored as text. Then in column B write =DATEVALUE(A1) and in column C write =TIMEVALUE(A1).

Then in column D do =B1+C1 to add the numerical formats of the date and time.

Finally, copy the values from D into column E by right clicking in column E and select Paste Special -> Paste as Values.

Highlight the numerical values in column E and change the data type to date - I prefer using a custom date of the form YYYY-MM-DD HH:MM:SS.

puiu
  • 726
  • 9
  • 18