CODEWITHSUNDEEP
stringdateexcelcastingvba
user3385769
user3385769

Reputation: 161

date format to string

I have created this sub with the intention of taking the Default American Date Convention, Changing it to a British format "yyyy-mm-dd" and then casting to a string. I want to change the format before converting to a string because it is easier to work with for my purpose. However, the string appears to be preserved in the original American format although the cell format has been changed. How can I solve this and what is reason it is doing this?

Sub Changedate()

    Sheet1.Cells(1, 3).Select
    Selection.NumberFormat = "yyyy-mm-dd;@"
    Sheet1.Cells(1, 4).Select
    Selection.NumberFormat = "yyyy-mm-dd;@"

    Dim firstDate As String
    Dim secondDate As String

    firstDate = Str(Sheet1.Cells(1, 3).Value)
    secondDate = Str(Sheet1.Cells(1, 4).Value)

    MsgBox firstDate

    Sheet1.Cells(1, 3).Select
    Selection.NumberFormat = "m/d/yyyy"
    Sheet1.Cells(1, 4).Select
    Selection.NumberFormat = "m/d/yyyy"

End Sub

Upvotes: 0

Views: 75

Answers (2)

user1759942
user1759942

Reputation: 1350

what you can also try is to use the .text property instead of .value. .value gets the cells contents without formatting. so you may see the string formatted incorrectly using .value. .text gets the cell contents with formatting (watch the run time, if this is running in a loop is may start to take a long time.) .text is the slowest method for getting cell contents, though with only a few rows or runs it wont make much difference

Upvotes: 1

avb
avb

Reputation: 1753

You should use:
firstDate = format(Sheet1.Cells(1, 3).Value, "yyyy-mm-dd")
Excel date is just a number and changing NumberFormat only affects how it is displayed. Str() probably uses your system default date format, you must use Format() to force it.

Upvotes: 1

Related Questions