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

[TABLE="width: 177"]
<tbody>[TR]
[TD]INPUT DATA[/TD]
[TD]OUTPUT DATA[/TD]
[/TR]
[TR]
[TD="align: right"]01-03-18[/TD]
[TD="align: right"]03-01-18[/TD]
[/TR]
[TR]
[TD="align: right"]02-03-18[/TD]
[TD="align: right"]03-02-18[/TD]
[/TR]
[TR]
[TD="align: right"]03-03-18[/TD]
[TD="align: right"]03-03-18[/TD]
[/TR]
[TR]
[TD="align: right"]04-03-18[/TD]
[TD="align: right"]03-04-18[/TD]
[/TR]
[TR]
[TD="align: right"]05-03-18[/TD]
[TD="align: right"]03-05-18[/TD]
[/TR]
[TR]
[TD="align: right"]06-03-18[/TD]
[TD="align: right"]03-06-18[/TD]
[/TR]
[TR]
[TD="align: right"]07-03-18[/TD]
[TD="align: right"]03-07-18[/TD]
[/TR]
[TR]
[TD="align: right"]08-03-18[/TD]
[TD="align: right"]03-08-18[/TD]
[/TR]
[TR]
[TD="align: right"]09-03-18[/TD]
[TD="align: right"]03-09-18[/TD]
[/TR]
[TR]
[TD="align: right"]10-03-18[/TD]
[TD="align: right"]03-10-18[/TD]
[/TR]
[TR]
[TD="align: right"]11-03-18[/TD]
[TD="align: right"]03-11-18[/TD]
[/TR]
[TR]
[TD="align: right"]12-03-18[/TD]
[TD="align: right"]03-12-18[/TD]
[/TR]
[TR]
[TD="align: right"]13-03-18[/TD]
[TD]13-03-2018[/TD]
[/TR]
[TR]
[TD="align: right"]14-03-18[/TD]
[TD]14-03-2018[/TD]
[/TR]
[TR]
[TD="align: right"]15-03-18[/TD]
[TD]15-03-2018[/TD]
[/TR]
[TR]
[TD="align: right"]16-03-18[/TD]
[TD]16-03-2018[/TD]
[/TR]
[TR]
[TD="align: right"]17-03-18[/TD]
[TD]17-03-2018[/TD]
[/TR]
[TR]
[TD="align: right"]18-03-18[/TD]
[TD]18-03-2018[/TD]
[/TR]
[TR]
[TD="align: right"]19-03-18[/TD]
[TD]19-03-2018[/TD]
[/TR]
[TR]
[TD="align: right"]20-03-18[/TD]
[TD]20-03-2018[/TD]
[/TR]
[TR]
[TD="align: right"]21-03-18[/TD]
[TD]21-03-2018[/TD]
[/TR]
[TR]
[TD="align: right"]22-03-18[/TD]
[TD]22-03-2018[/TD]
[/TR]
[TR]
[TD="align: right"]23-03-18[/TD]
[TD]23-03-2018[/TD]
[/TR]
[TR]
[TD="align: right"]24-03-18[/TD]
[TD]24-03-2018[/TD]
[/TR]
[TR]
[TD="align: right"]25-03-18[/TD]
[TD]25-03-2018[/TD]
[/TR]
[TR]
[TD="align: right"]26-03-18[/TD]
[TD]26-03-2018[/TD]
[/TR]
[TR]
[TD="align: right"]27-03-18[/TD]
[TD]27-03-2018[/TD]
[/TR]
[TR]
[TD="align: right"]28-03-18[/TD]
[TD]28-03-2018[/TD]
[/TR]
[TR]
[TD="align: right"]29-03-18[/TD]
[TD]29-03-2018[/TD]
[/TR]
</tbody>[/TABLE]

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!).