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
I got the solution here :- excelfunctions.net/convert-date-to-text.html — Anup

PaichengWu PaichengWu · Accepted Answer · 2016-06-21T10:01:12

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

Excel - Format Date Value to Text

1 Answers