CODEWITHSUNDEEP
excelformat-conversionvba
Ehsan
Ehsan

Reputation: 15

Convert a string value to a date value in vba

In order to ease the data entry, I am allowing users to enter the date as "ddmmyyyy" without "/", e.g, "12032017" . After the entry I want to update the target cell with value "dd/mm/yyyy", e.g, "12/03/2017".
At the start the format of the target cell is set to "General" but as soon as the date value is calculated, format of the cell is automatically changed to "dd/m/yyyy". I have tried to use both the General and date format. Below is the VBA code

    If Not Intersect(Target, Range("D11:D510")) Is Nothing Then
    If Not (Target.Value = "") Then
    Application.EnableEvents = False   ' very important
    Target.Value = Left(Target.Value, 2) & "/" & Mid(Target.Value, 3, 2) & 
    "/" & Right(Target.Value, 4)
    Application.EnableEvents = True   'very important
    End If
    End If

Upvotes: 1

Views: 1964

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

You are still returning a string that looks like a date and not an actual date, Use DateSerial:

If Not Intersect(Target, Range("D11:D510")) Is Nothing Then
    Target.NumberFormat = "@"
    If Not (Target.Value = "") Then
        Application.EnableEvents = False   ' very important
        Target.Value = DateSerial(Right(Target.Value, 4), Mid(Target.Value, 3, 2), Left(Target.Value, 2))
        Target.NumberFormat = "dd/mm/yyyy"
        Application.EnableEvents = True   'very important
    End If
End If

Upvotes: 1

Related Questions