Convert a string value to a date value in vba

Question

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

12032017 is not a valid recognizable date that Excel will automatically change, so simply changing the format will not work. You will need vba in a Worksheet_Change event, that parses the string and returns a date, that you then can format properly. — Scott Craner
– Scott Craner, Commented Jul 11, 2017 at 13:59
@ScottCraner, thank you for the reply. Yes, I am using a macro and below is the 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 — Ehsan
– Ehsan, Commented Jul 11, 2017 at 14:53
Please put the code in the Original Post using edit not in comments. — Scott Craner
– Scott Craner, Commented Jul 11, 2017 at 14:53
Just added the code in the original post. please have a look — Ehsan
– Ehsan, Commented Jul 11, 2017 at 14:57

Scott Craner · Accepted Answer · 2017-07-11 19:40:31Z

1

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

edited Jul 11, 2017 at 19:40

answered Jul 11, 2017 at 15:06

Scott Craner

154k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ehsan Over a year ago

Scott, I am getting the overflow error on the If Not (Target.Value = "") Then statement

Ehsan Over a year ago

Its working fine now by setting the format of the target cell as general instead of date. Thanks for your time

Ehsan Over a year ago

Scott, I am getting the overflow error whenever I try to change the value second time. For example, at the start the Target cell format is set to "General" and the Macro works fine. This Target.NumberFormat = "dd/mm/yyyy"

Scott Craner Over a year ago

@Ehsan see edit, we need to change the format back to text, before doing the testing.

Ehsan Over a year ago

Yes Scott you are right. I did this and It was working fine. thanks for your help

Collectives™ on Stack Overflow

Convert a string value to a date value in vba

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related