Excel Date to String conversion

Question

Hi

I have a Date value like

01/01/2010 14:30:00

in a cell in Excel sheet. 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?

Thank you! Chaitanya

Answer 1

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

Answer 2

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.

Answer 3

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