Mar 30 2023 08:06 AM
Hello all,
I currently have my dates typed in the column as 3-12 because the worksheet already has the year on it. I have it set up that way so all I have to do is type in "0312" instead of "03122023" and the result is 3-12. I want to know if it is possible to type in "0312" and the result be 3-12-2023 without having to type in 2023.
I appreciate your time and thank you.
Mar 30 2023 08:28 AM
I'll assume that you use dd-mm-yyyy as date format. See below the code for the change you need to make if you use mm-dd-yyyy.
Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the following code into the worksheet module.
Switch back to Excel.
Save the workbook as a macro-enabled workbook (*.xlsm).
Make sure that you allow macros when you open it.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim cel As Range
Dim d As Long
Dim m As Long
Dim y As Long
' The range in which you want to enter dates as 0312
Set rng = Range("A2:A100")
If Intersect(rng, Target) Is Nothing Then Exit Sub
y = Year(Date)
On Error GoTo ExitHere
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each cel In Intersect(rng, Target)
If Len(cel.Formula) > 0 Then
Select Case Len(cel.Formula)
Case 3 ' For example 312
d = Left(cel.Formula, 1)
m = Right(cel.Formula, 2)
cel.Value = DateSerial(y, m, d)
Case 4 ' For example 1112
d = Left(cel.Formula, 2)
m = Right(cel.Formula, 2)
cel.Value = DateSerial(y, m, d)
Case Else ' Invalid input
Application.Undo
End Select
End If
Next cel
ExitHere:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Warning: if you enter a value that is not valid as a date, the result will be unexpected.
If you use mm-dd-yyyy as date format, switch the calculations of d and m, for example
m = Left(cel.Formula, 1)
d = Right(cel.Formula, 2)
and
m = Left(cel.Formula, 2)
d = Right(cel.Formula, 2)