Macro to Store Date as Text

[seenai]

Hi,

I need to store the Excel DATE in selected RANGE as TEXT format. (dd/mm/yyyy).

It means the date is displayed in dd/mm/yyyy format. But it is stored as text.

Thanks in advance.

- B.Srinivasa Rao

 

[Joe4]

Let's say that you wanted to save it to cell C1. Then this code snippet would do that:

    Range("C1").NumberFormat = "@"
    Range("C1") = Format(Date, "dd/mm/yyyy")

 

[seenai]

I have used the below code. But the dates upto 12-03-18 came in date format only. Other data is OK as Text data. :
Help me to modify the code.

Sub StoreDateasText()
Dim Cell As Range
Dim s As String
Selection.NumberFormat = "dd/mm/yyyy"
For Each Cell In Selection
        With Cell
            s = Cell.Text
            .NumberFormat = "@"
            Cell.ClearFormats
            Cell.Value = s
        End With
    Next Cell
End Sub

INPUT DATA	OUTPUT DATA
01-03-18	03-01-18
02-03-18	03-02-18
03-03-18	03-03-18
04-03-18	03-04-18
05-03-18	03-05-18
06-03-18	03-06-18
07-03-18	03-07-18
08-03-18	03-08-18
09-03-18	03-09-18
10-03-18	03-10-18
11-03-18	03-11-18
12-03-18	03-12-18
13-03-18	13-03-2018
14-03-18	14-03-2018
15-03-18	15-03-2018
16-03-18	16-03-2018
17-03-18	17-03-2018
18-03-18	18-03-2018
19-03-18	19-03-2018
20-03-18	20-03-2018
21-03-18	21-03-2018
22-03-18	22-03-2018
23-03-18	23-03-2018
24-03-18	24-03-2018
25-03-18	25-03-2018
26-03-18	26-03-2018
27-03-18	27-03-2018
28-03-18	28-03-2018
29-03-18	29-03-2018

<tbody>
</tbody>

Let's say that you wanted to save it to cell C1. Then this code snippet would do that:

    Range("C1").NumberFormat = "@"
    Range("C1") = Format(Date, "dd/mm/yyyy")

 

[seenai]

Hi, Solved the problem. Thanks for support.

Sub StoreDateasText()
Dim Cell As Range
Dim s As String
Selection.NumberFormat = "dd/mm/yyyy"
For Each Cell In Selection
        With Cell
            s = Cell.Text
            .NumberFormat = "@"
            Cell.Value = s
        End With
    Next Cell
End Sub

 

[Joe4]

Yes, you were clearing the cell format right after you were setting it before (which kind of defeats the purpose of setting it in the first place!).