Быстрый ввод даты и времени без разделителей

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
            End If
            On Error GoTo ErrorHandlerDivision
            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)))
ErrorHandlerDivision:
        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

.Value = CDate(DateSerial(Year(dDate), Month(dDate)-1, Day(dDate)))

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      End If      On Error GoTo ErrorHandlerDivision      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)))ErrorHandlerDivision:     End With    End If    If Not Intersect(Target, Range("K16:AO41")) Is Nothing Then     With Target      vVal = Format(.Value, "0000")2      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 = TrueEnd Sub

If Not Intersect(Target, Range("A2:A10")) Is Nothing Then
 

Dim Num1 As Range
Num1 = InputBox("Текст в окошке", "Название окошка")
If Not Intersect(Target, Range(Num1)) Is Nothing Then  

Private Sub Worksheet_Change(ByVal Target As Range)
Dim vVal
Dim StrVal As String
Dim dDate As Date
On Error GoTo ErrorHandler ' Обработка ошибок
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
ExitHandler: ' Возвращение функции Application.EnableEvents
Application.EnableEvents = True
Exit Sub

ErrorHandler: ' Обработка ошибок
Resume ExitHandler

End Sub
 

function onEdit(e) {
  var range = e.range;
  var sheet = range.getSheet();
  if(sheet.getName() === 'Лист1' && ((range.getColumn() === 3 && range.getRow() >= 2 && range.getRow() <= 9999) || (range.getColumn() === 5 && range.getRow() >= 2 && range.getRow() <= 9999))) {
    var value = e.value;
    var regex = /^(\d{1}|\d{2})(\d{2})(\d{2}|\d{4})$/;
    if(regex.test(value)) {
      var match = regex.exec(value);
      var day = match[1];
      var month = match[2];
      if (month[0] === '0') {
        month = month[1];
      }
      month = parseInt(month) - 1; // уменьшаем значение месяца на единицу, иначе он будет больше чем нужно
      var year = match[3];
      if(year.length === 2) {
        var current_year = parseInt(new Date().getFullYear().toString().substr(-2));
        var input_year = parseInt(year);
        year = input_year <= current_year + 29 ? '20' + year : '19' + year;  // если год введён 2 цифрами, правим на 4
      }
      var date = new Date(year, month, day);
      date.setDate(date.getDate()+1); // добавляем один день, иначе он будет уменьшен на единицу
      var dateString = Utilities.formatDate(date, 'GMT', 'dd.MM.yyyy');
      sheet.getRange(range.getRow(), range.getColumn()).setValue(dateString);
    }
  }
}

if(sheet.getName() === 'Лист1' && range.getColumn() === 3 && range.getRow() > 1  ) { 

function onEdit(e) {
  var range = e.range;
  var idRow = e.range.getRow(); // получаем номер текущей строки
  var idCol = e.range.getColumn();  // получаем номер текущего столбца
  var sheet = range.getSheet();
  if(sheet.getName() === 'Лист1' && ((idCol === 3 && idRow > 1 ) || (idCol === 4 && idRow > 1 ) || (idCol === 5 && idRow > 1 ))) {
    var sheet = e.source.getActiveSheet();
    var value = sheet.getRange(idRow, idCol).getDisplayValue(); // получаем введённое значение в ячейку не в том виде, в каком оно хранится, а в том как оно отображается на экране
    var regex = /^(\d{1}|\d{2})(\d{2})(\d{2}|\d{4})$/;
    if (value.includes(".")) return; // проверяем на наличие точек в введённой строке, и если находим прерываем выполнение скрипта
    if(regex.test(value)) {
      var match = regex.exec(value);
      var day = match[1];
      var month = match[2];
      if (month[0] === '0') {
        month = month[1];
      }
      month = parseInt(month) - 1;
      var year = match[3];
      if(year.length === 2) {
        var current_year = parseInt(new Date().getFullYear().toString().substr(-2));
        var input_year = parseInt(year);
        year = input_year <= current_year + 29 ? '20' + year : '19' + year;
      }
      var date = new Date(year, month, day);
      date.setDate(date.getDate()+1); // добавляем один день, иначе он будет уменьшен на единицу
      var dateString = Utilities.formatDate(date, 'GMT', 'dd.MM.yyyy');
      sheet.getRange(range.getRow(), range.getColumn()).setValue(dateString);
    }
  }
}
 

function onEdit(e) { 
var range = e.range; 
var idRow = range.getRow(); 
var idCol = range.getColumn(); 
var sheet = range.getSheet();
  if (sheet.getName() === 'Лист1' && (idCol === 3 || idCol === 4 || idCol === 5) && idRow > 1) { 
    var value = sheet.getRange(idRow, idCol).getDisplayValue();
    if (value.includes(".")) return; // если есть точки — пропускаем
    var currentYearFull = new Date().getFullYear().toString(); // например, "2025" 
    var currentYearShort = currentYearFull.substr(2, 2); // например, "25"
    value = value.trim();
    var timeZone = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone();
    if (/^\d{2}$/.test(value)) { 
      // 2-значное число, например "83" -> день 8, месяц 3 
      var day = value.substr(0, 1); 
      var month = value.substr(1, 1); 
      day = day.length === 1 ? '0' + day : day; 
      var year = parseInt(currentYearFull, 10);
      var date = new Date(year, parseInt(month, 10) - 1, parseInt(day, 10));
      if (isValidDate(date, day, month, year)) { 
        var dateString = Utilities.formatDate(date, timeZone, 'dd.MM.yyyy'); 
        sheet.getRange(idRow, idCol).setValue(dateString); 
      } 
    } else if (/^\d{3}$/.test(value)) { 
      // 3-значное число 
      // Проверяем, если первые две цифры >= 10 и последняя цифра от 1 до 9 
      var dayPart = value.substr(0, 2); 
      var monthPart = value.substr(2, 1);
      var dayNum = parseInt(dayPart, 10); 
      var monthNum = parseInt(monthPart, 10); 
      var year = parseInt(currentYearFull, 10);
      if (dayNum >= 10 && monthNum >= 1 && monthNum <= 9) { 
        // Случай: день 28, месяц 1 (например 281) 
        var day = dayPart; 
        var month = monthPart; 
      } else { 
        // Иначе считаем, что день — первая цифра, месяц — две следующие (как раньше) 
        day = value.substr(0, 1); 
        month = value.substr(1, 2); 
        day = day.length === 1 ? '0' + day : day; 
      }
      var date = new Date(year, parseInt(month, 10) - 1, parseInt(day, 10));
      if (isValidDate(date, day, month, year)) { 
        var dateString = Utilities.formatDate(date, timeZone, 'dd.MM.yyyy'); 
        sheet.getRange(idRow, idCol).setValue(dateString); 
      } 
    } else if (/^\d{4}$/.test(value)) { 
      // Четырёхзначное число, например "1210" -> день 12, месяц 10, год текущий 
      var day = value.substr(0, 2); 
      var month = value.substr(2, 2); 
      var year = parseInt(currentYearFull, 10);
      var date = new Date(year, parseInt(month, 10) - 1, parseInt(day, 10));
      if (isValidDate(date, day, month, year)) { 
        var dateString = Utilities.formatDate(date, timeZone, 'dd.MM.yyyy'); 
        sheet.getRange(idRow, idCol).setValue(dateString); 
      } 
    } else { 
      // Прежний функционал для 6 или 8 цифр 
      var regex = /^(\d{1,2})(\d{2})(\d{2}|\d{4})$/; 
      if (regex.test(value)) { 
        var match = regex.exec(value); 
        var day = match[1]; 
        var month = match[2]; 
        if (month[0] === '0') { 
          month = month[1]; 
        } 
        month = parseInt(month, 10) - 1; 
        var year = match[3]; 
        if (year.length === 2) { 
          var current_year_short = parseInt(currentYearShort, 10); 
          var input_year = parseInt(year, 10); 
          year = input_year <= current_year_short + 29 ? '20' + year : '19' + year; 
        } 
        var date = new Date(year, month, parseInt(day, 10)); 
        if (isValidDate(date, day, month + 1, year)) { 
          var dateString = Utilities.formatDate(date, timeZone, 'dd.MM.yyyy'); 
          sheet.getRange(idRow, idCol).setValue(dateString); 
        } 
      } 
    } 
  } 
}
// Проверка корректности даты 
function isValidDate(d, day, month, year) { 
  return d && d.getFullYear() == year && d.getMonth() + 1 == parseInt(month, 10) && d.getDate() == parseInt(day, 10); 
} 

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A2:A19")) Is Nothing And Target.Cells.Count = 1 Then
        On Error GoTo ErrorHandler
        Application.EnableEvents = False

        Dim inputVal As String
        Dim d As Integer, m As Integer, y As Integer
        Dim resultDate As Date
        Dim currentYear As Integer

        currentYear = Year(Date)

        inputVal = CStr(Target.Value)
        inputVal = Replace(inputVal, ".", "")
        inputVal = Replace(inputVal, "/", "")
        inputVal = Trim(inputVal)

        If Not IsNumeric(inputVal) Then GoTo ExitHandler

        Select Case Len(inputVal)
            Case 2
                d = CInt(Left(inputVal, 1))
                m = CInt(Right(inputVal, 1))
                y = currentYear

            Case 3
                d = CInt(Left(inputVal, 1))
                m = CInt(Right(inputVal, 2))
                If m < 1 Or m > 12 Then
                    d = CInt(Left(inputVal, 2))
                    m = CInt(Right(inputVal, 1))
                    If m = 0 Then m = 10
                End If
                y = currentYear

            Case 4
                d = CInt(Left(inputVal, 2))
                m = CInt(Right(inputVal, 2))
                y = currentYear

            Case 5
                d = CInt(Left(inputVal, 1))
                m = CInt(Mid(inputVal, 2, 2))
                y = 2000 + CInt(Right(inputVal, 2))
                If m < 1 Or m > 12 Then
                    d = CInt(Left(inputVal, 2))
                    m = CInt(Mid(inputVal, 3, 1))
                    y = 2000 + CInt(Right(inputVal, 2))
                End If

            Case 6
                d = CInt(Left(inputVal, 2))
                m = CInt(Mid(inputVal, 3, 2))
                y = 2000 + CInt(Right(inputVal, 2))

            Case 8
                d = CInt(Left(inputVal, 2))
                m = CInt(Mid(inputVal, 3, 2))
                y = CInt(Right(inputVal, 4))

            Case Else
                GoTo ExitHandler
        End Select

        If d >= 1 And d <= 31 And m >= 1 And m <= 12 Then
            resultDate = DateSerial(y, m, d)
            Target.NumberFormat = "dd.mm.yyyy"
            Target.Value = resultDate
        End If

ExitHandler:
        Application.EnableEvents = True
        Exit Sub

ErrorHandler:
        Resume ExitHandler
    End If
End Sub
 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("A2:A19")) Is Nothing And Target.Cells.Count = 1 Then
        Dim inputVal As String
        Dim d As Integer, m As Integer, y As Integer
        Dim currentYear As Integer
        Dim resultDate As Date

        currentYear = Year(Date)
        inputVal = InputBox("Введите дату цифрами (например 130525 или 83):", "Ввод даты")

        If inputVal = "" Then Exit Sub
        If Not IsNumeric(inputVal) Then
            MsgBox "Ввод должен быть числом", vbExclamation
            Exit Sub
        End If

        inputVal = Trim(inputVal)

        On Error GoTo InvalidInput

        Select Case Len(inputVal)
            Case 2
                d = CInt(Left(inputVal, 1))
                m = CInt(Right(inputVal, 1))
                y = currentYear

            Case 3
                d = CInt(Left(inputVal, 1))
                m = CInt(Right(inputVal, 2))
                If m < 1 Or m > 12 Then
                    d = CInt(Left(inputVal, 2))
                    m = CInt(Right(inputVal, 1))
                    If m = 0 Then m = 10
                End If
                y = currentYear

            Case 4
                d = CInt(Left(inputVal, 2))
                m = CInt(Right(inputVal, 2))
                y = currentYear

            Case 5
                d = CInt(Left(inputVal, 2))
                m = CInt(Mid(inputVal, 3, 1))
                y = 2000 + CInt(Right(inputVal, 2))
                If m < 1 Or m > 12 Then
                    d = CInt(Left(inputVal, 1))
                    m = CInt(Mid(inputVal, 2, 2))
                End If

            Case 6
                d = CInt(Left(inputVal, 2))
                m = CInt(Mid(inputVal, 3, 2))
                y = 2000 + CInt(Right(inputVal, 2))

            Case 8
                d = CInt(Left(inputVal, 2))
                m = CInt(Mid(inputVal, 3, 2))
                y = CInt(Right(inputVal, 4))

            Case Else
                GoTo InvalidInput
        End Select

        If d >= 1 And d <= 31 And m >= 1 And m <= 12 Then
            resultDate = DateSerial(y, m, d)
            Target.NumberFormat = "dd.mm.yyyy"
            Target.Value = resultDate
        Else
            GoTo InvalidInput
        End If
    End If
    Exit Sub

InvalidInput:
    MsgBox "Неверный формат даты!", vbCritical
End Sub