CODEWITHSUNDEEP
excelvba
Ryan Colin
Ryan Colin

Reputation: 1

Is there a way to convert a Long Date to short date in Excel?

I have an exported file that gives me a list of long dates in format General. ex: Friday, August 28, 2020

I am trying to convert them into short dates. I've tried using CDate function, but I get a mismatch error. I find this odd because the cell has the exact long date form.

I've tried running a ton of code. Here's the most recent one I tried. It changs the cell format into Long Date. then uses Cdate and gets a mismatch error.

Sub formatdate()
'
' Macro1 Macro
'


    Range("J2").Select
    Selection.NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"

    MsgBox (CDate(Range("J2")))
End Sub

Any help would be much appreciated!

Upvotes: 0

Views: 1120

Answers (1)

Tim Williams
Tim Williams

Reputation: 166146

As @scottcraner says:

Dim v
With Range("J2")
    v = .Value
    v = Mid(v, InStr(v, ",") + 1, 100)
    .Value = DateValue(v)
End With

Upvotes: 1

Related Questions