If you often need to enter dates and times in cells, then you should like the idea of writing them abbreviated, without dots, fractions, colons – just as a number. So that in a given range of sheet cells, for example, the number 250699 automatically turns into 25.06.1999/1125/11, and 25 into XNUMX:XNUMX.
To do this, click on the sheet tab where dates and times will be entered and select the command Source text (Source Code). In the Visual Basic Editor window that opens, copy and paste the following code:
Private Sub Worksheet_Change(ByVal Target As Range) Dim vVal Dim StrVal As String Dim dDate As Date If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Range("A2:A10")) Is Nothing Then With Target StrVal = Format(.Text, "000000") If IsNumeric(StrVal) And Len(StrVal) = 6 Then Application.EnableEvents = False dDate = DateValue(Left(StrVal, 2) & "/" & Mid(StrVal, 3, 2) & "/" & Right(StrVal, 2)) .NumberFormat = "dd/mm/yyyy" .Value = CDate(DateSerial(Year(dDate), Month(dDate), Day(dDate))) End If End With End If If Not Intersect(Target, Range("B2:B10")) Is Nothing Then With Target vVal = Format(.Value, "0000") If IsNumeric(vVal) And Len(vVal) = 4 Then Application.EnableEvents = False .Value = Left(vVal, 2) & ":" & Right(vVal, 2) .NumberFormat = "[h]:mm" End If End With End If Application.EnableEvents = True End Sub Replace ranges A2:A10 and B2:B10 with your sheet areas, where dates and times will be entered in a similar way, respectively.
- Pop-up Calendar DatePicker to quickly enter dates into cells
- Pop-up calendar in PLEX add-on
- Automatic date entry when entering data into a cell
- What are macros, where to insert macro code in VBA, how to use them.