CODEWITHSUNDEEP
excelvba
W-hit
W-hit

Reputation: 361

VBA Excel Convert Date Time to Text

I'd like to convert a date time to text.

Example: 10/22/2019 2:10 PM should be converted to 43760.59028. I know how to do this if I was trying to convert a cell in a spreadsheet, but I'm trying to convert a date time variable.

I've tried: vdate = Format(CDate("10/22/2019 2:10 PM"), "@")

I end up with a string rather than the text conversion to a number.

How do I convert a date or date time and store the value in a variable?

Upvotes: 0

Views: 3091

Answers (2)

office helpdesk
office helpdesk

Reputation: 1

this example may be help you 

Sub convert_date2txt()
     Dim theRange as Range
     Set theRange = Selection
     Dim sDate As String
     For Each theCell In theRange
         sDate=Format(theCell,"dd/mm/yyyy")
         theCell.Value = sDate
         theCell.NumberFormat="@"
     Next
End Sub

Upvotes: 0

Mathieu Guindon
Mathieu Guindon

Reputation: 71217

Format yields a String, you don't want that.

Declare vdate as a Double:

Dim vdate As Double

Now this will work:

vdate = CDate("10/22/2019 2:10 PM")

...but that's an implicit type conversion from Date to Double; you can make it explicit:

vdate = CDbl(CDate("10/22/2019 2:10 PM"))

...as Scott mentioned in the comments

Upvotes: 1

Related Questions