Change content of cell with new value

JohanT

New Member
Joined
May 8, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Goodday,

I'm struggling for a few days with a problem.
I need to input a lot of dates. I want to simplify the entries by typing 120521 in the cell and the cell gets the new value of 12/05/2021
The conversion part is done... but when I try to put the new date in the old cell... I get or errors type mismatch or loops of death. (excel crash) or nothing.
Below the code, I have so far. I put some debug.print so you can see the results.

Would appreciate some help or tips.

VBA Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 1 Then
        Dim rawdate As String
        Dim newdate As String
        Dim printdate As String
         If Target.Value <> "" Then
            Debug.Print Target.Value
            rawdate = Target.Value
            Debug.Print rawdate
            If Len(rawdate) = 6 Then 
            newdate = Left(rawdate, 2) & "/" & Mid(rawdate, 3, 2) & "/20" & Right(rawdate, 2)
            Debug.Print newdate
            Else
            newdate = "0" & Left(rawdate, 1) & "/" & Mid(rawdate, 2, 2) & "/20" & Right(rawdate, 2)
            Debug.Print newdate
        End If
        Debug.Print Target.Address
End If
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sorry, missed some code during copy.

VBA Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 1 Then
        Dim rawdate As String
        Dim newdate As String
        Dim printdate As String
         If Target.Value <> "" Then
            Debug.Print Target.Value
            rawdate = Target.Value
            Debug.Print rawdate
            If Len(rawdate) = 6 Then
            newdate = Left(rawdate, 2) & "/" & Mid(rawdate, 3, 2) & "/20" & Right(rawdate, 2)
            Debug.Print newdate
            Else
            newdate = "0" & Left(rawdate, 1) & "/" & Mid(rawdate, 2, 2) & "/20" & Right(rawdate, 2)
            Debug.Print newdate
        End If
        Debug.Print Target.Address
End If
End If

End Sub
 
Upvote 0
Hi, for an OS date setting as dd/mm/yyyy or jj/mm/aaaa or … whatever :​
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    With Target(1)
            If .Column > 1 Then Exit Sub
        If IsNumeric(.Text) Then
                        N& = Len(.Text) - 4
            Select Case N
                   Case 1, 2
                        Application.EnableEvents = False
                       .FormulaLocal = Format(.Text, String(N, "@") & "/@@/20@@")
                        Application.EnableEvents = True
            End Select
        End If
    End With
End Sub
 
Upvote 0
Correction : previous post should work whatever the date setting whatever US or 'UK like' according to local date setting …​
And with Option Explicit you must add this codeline : Dim N& …​
 
Last edited:
Upvote 0
Dear Marc, many thanks for this excellent solution.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top