Excel - Format Date Value to Text

Question

I am having dates in my excel sheet 2013. I need to convert them to TEXT. So when I go to Format Cells & select TEXT option, then the value changes to something else.

For e.g If i have date as 01/01/2016, when i change it to TEXT the value becomes '42370'.

How to change the format of cell to TEXT by retaining the correct value?

I tried doing formula =TEXT(C6,"dd/mm/yyyy") but formula has to be written in a different cell. What if i have to change it in the same cell?

Add an apostrophe before the date: `'01/05/2016`. `=ISTEXT(A1)` will return TRUE then, but `A1+1` will still return `02/05/2016` — Darren Bartrup-Cook, Jun 21 '16 at 09:11
I got the solution here :- http://www.excelfunctions.net/convert-date-to-text.html — Anup, Jun 21 '16 at 09:48

PaichengWu · Answer 1 · 2016-06-21T10:19:13.783

0

You may try following steps:

select cells what you want to change.
mouse right click > Format Cells
Number tag > Custom
Write dd/mm/yyyy in Type box > OK

other method

sub DatetoString()
    dim rng as range

    for each rng in range("A1:A10")
        rng.value = format(rng.value, "dd/mm/yyyy")
    next rng

end sub

edited Jun 21 '16 at 10:19

answered Jun 21 '16 at 10:01

PaichengWu

2,649
1
14
28

As i did the above, the format of the cell still remains as `Date` – Anup Jun 21 '16 at 10:04
So you want to change them into string at where they are? – PaichengWu Jun 21 '16 at 10:14

Excel - Format Date Value to Text

1 Answers1