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

Если Вам часто приходится вводить даты и время в ячейки, то Вам должна понравиться идея писать их сокращенно, без точек-дробей-двоеточий - просто как число. Чтобы в заданном диапазоне ячеек листа, например, число 250699 автоматически превращалось в 25.06.1999, а 1125 в 11:25.

quick-date-enter.gif

Для этого щелкните по ярлычку листа, куда будут вводиться даты и время и выберите команду Исходный текст (Source Code). В открывшееся окно редактора Visual Basic скопируйте и вставьте следующий код:

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

Диапазоны A2:A10 и B2:B10 замените на свои области листа, куда будут подобным образом вводиться даты и время, соответственно.

Ссылки по теме


Goleff
07.10.2012 13:13:57
Макрос вообще не нужен если формат ячейки поставить "Дата", а значения вводить: 25-06-1999 или 25/06/1999 или 25,06,1999. Если набрать 25/6 или 25-06 ..., то год подставится текущий.
07.10.2012 13:18:42
Так в том и фишка, чтобы лишних знаков препинания не вводить.
елена
07.10.2012 13:14:31
Обалдеть, класс!!! Спасибо огромное автору!!!
И что поразительно у меня это работает!!!
AKSENOV048
07.10.2012 13:18:02
ввожу 250811 выдает 10.09.2586
07.10.2012 13:19:56
Не ставьте заранее для ячейки ввода формат даты. Иначе все введенное Excel автоматически преобразует в дату, а точнее - в свой специфический код даты. И макрос работает дальше уже с ним, а не с введенными вами цифрами.
Алексей
07.10.2012 13:21:24
Можно ли вводить текущую дату, как-то попроще...
07.10.2012 13:22:09
CTRL+Ж либо CTRL+SHIFT+4
01.02.2013 12:55:23
у меня не работает даже в скачанном примере. Если не менять формат ячейки, и ввести, к примеру, 110887, то ничего не меняется, цифры в таком формате и остаются. Если формат ячейки поменять на формат даты, то выдает 06.08.2203
01.02.2013 13:53:05
Защита от макросов выключена? Файл - Параметры - Центр Управления Безопасностью - Параметры Центра Управления - Макросы - Разрешить все макросы. И перезагрузиться.
24.02.2013 05:18:41
Подскажите, пожалуйста, почему не работает на всю книгу? Если создаю для одного листа - все в порядке. Статью по созданию макросов прочитала, но все равно не получается.
08.03.2013 15:10:29
Потому, что этот макрос написан для листа и вставлять его надо в модули листов, а не в модуль ЭтаКнига.
07.03.2013 01:02:53
здравствуйте. макрос отличный на много сокращает количество нажатий кнопок на клавиатуре и соответственно времени рутины. при его использовании столкнулся с тем что при внесении даты(например 310113) если ошибочно внес не 6 знаков(например 3113), а другое количество выдает ошибку необходимости отладки кода макроса по 12 строке и макрос перестает работать.подскажите, что можно сделать ?
08.03.2013 15:09:10
В случае занесения не 6 символов ошибку выдавать не должно - в 10-й строке идет проверка на наличие именно 6 символов, и если количество введенных цифр не равно 6 - макрос заканчивает работу и ничего не происходит.
14.03.2013 20:46:52
Добрый день!
На работе этот макрос используем на нескольких компьтерах ошибка «runtimeerror ‘13’ typemismatch» появляется при ошибочном внесении например «1»или «131312».
После этого макрос перестает работать. Макрос включается только после  перезапускаExcel.
Я не могу разобраться почему так происходит.
Подскажите,что нужно сделать что бы:
1.     -макрос снова начинал работать без перезапуска Excel
2.     -  не появлялась на экране ошибка «runtimeerror ‘13’ typemismatch» которая приводит в панику некоторых пользователей.
3.    -   и посмотрите пожалуйста макрос предложенный Вами чуть чуть мной переделанный. Какие в нем недостатки. В VBA  профан за ранее извеняюсь за назойливость.
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


(файл в котором у меня ошибка прикрепить не могу не нахожу кнопки "прикрепить файл";)
14.03.2013 22:04:02
А ее тут и нет - это комментарии к приему, а не форум. Лучше создайте тему на форуме и прикрепите ваш файл с макросом - а мы его покритикуем ;)

А зависание макроса происходит, скорее всего, из за того, что вы в коде отключаете обработку событий Application.EnableEvents=False, а после возникновения ошибки она у вас обратно не включается.
22.03.2013 14:01:28
Сразу Спасибо за сайт!!!! оч помогает.

а как переделать макрос для времени, если вводится формат 21.15 (ну или любой другой знак, всякое бывает), ну а нужен обычный  21:15
17.06.2013 16:58:55
dDate = DateValue(Left(StrVal, 2) & "/" & Mid(StrVal, 3, 2) & "/" & Right(StrVal, 2))
12 строка подсвечивается для отладки, когда макрос перестаёт работать.

Это происходит, как я заметил, в случае:
1. Сортировки таблицы, несколько столбцов которой заполнены при помощи данного макроса датой и временем.
2. После ошибочного ввода в рабочем диапазоне макроса даты с разделителями, удаления ошибчной записи и последующей попытки заполнить ячейку не применяя разделители, при помощи макроса.
21.08.2013 14:17:52
Добрый день! не в коем случаи не хочу обидеть автора статьи, но у меня есть один трюк именно для того, чтобы лишний раз не нажимать shift и какой либо знак. он делается очень легко и для даты и для времени, без всякого там кода VB, сам я ламер в VB поэтому вечно ищу более легкие пути, так вот я делаю так:

для даты:
в формате ячейки -> все форматы 00\.00\.0000
пример:
12122012 -> 12.12.2012

для времени -> все форматы 00\:00
пример:
1212 -> 12:12

после слеша влево можно поставить любой знак, который предпочтителен для Вас.

Надеюсь Вам понравится такая идея.
если у кого будут вопросы или захотите сказать спасибо, я в facebook Parviz Ruziev


удачи всем!!!
26.08.2013 10:54:27
Добрый день, описанный вами прием всего лишь придаст форму но не изменит сути, Excel не будет воспринимать введенное значение как дату.

Пример
Вы ввели значениеПрименили форматExcel думает что этоА надо чтобы думал что это
1212201212.12.2012Дата из далекого будущего, если конкретно то примерно через 33 тысячи лет, Excel не отразит эту дату заменив на #####41255
121212:120:00:00 и сколько то наносекунд0,51
26.08.2013 16:26:41
К сожалению, с отофрматированными вашим способом датами нельзя работать. Попробуйте вычесть из одной даты другую, чтобы посчитать количество дней - и получите ерунду. И ни одна функция из категории "Даты и время" с вашими "датами" работать не будет. Так что не советовал бы идти таким путем, при всем уважении.
12.11.2013 13:05:59
Посмею предложить свой вариант. )))

В формате ячейки (у меня это D2) выставляем формат типа:
00"."00"."0000

На проверку вводимых данных задаем условия проверки:
тип данных – другой; и на соответствие формуле в соответствующей ячейке (у меня это R2).

Формула такого вида:
=ЕСЛИ(ИЛИ(И(ДЛСТР(D2)=7;ДАТАЗНАЧ(ТЕКСТ(ДАТА(ПРАВСИМВ(D2;4);ПСТР(D2;2;2);СЦЕПИТЬ("0";ЛЕВСИМВ(D2)));"ДД.ММ.ГГГГ";))>=25569;ДАТАЗНАЧ(ТЕКСТ(ДАТА(ПРАВСИМВ(D2;4);ПСТР(D2;2;2);СЦЕПИТЬ("0";ЛЕВСИМВ(D2)));"ДД.ММ.ГГГГ";))<=44196;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;2;2);"00";))<=12);И(ДЛСТР(D2)=8;ДАТАЗНАЧ(ТЕКСТ(ДАТА(ПРАВСИМВ(D2;4);ПСТР(D2;3;2);ЛЕВСИМВ(D2;2));"ДД.ММ.ГГГГ";))>=25569;ДАТАЗНАЧ(ТЕКСТ(ДАТА(ПРАВСИМВ(D2;4);ПСТР(D2;3;2);ЛЕВСИМВ(D2;2));"ДД.ММ.ГГГГ";))<=44196;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))<=12;ИЛИ(ЕСЛИ(И(ИЛИ(ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))=1;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))=3;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))=5;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))=7;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))=8;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))=10;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))=12;);ЗНАЧЕН(ТЕКСТ(ЛЕВСИМВ(D2;2);"00";))<=31);ИСТИНА;ЛОЖЬ);ЕСЛИ(И(ИЛИ(ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))=4;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))=6;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))=9;ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))=11);ЗНАЧЕН(ТЕКСТ(ЛЕВСИМВ(D2;2);"00";))<=30);ИСТИНА;ЛОЖЬ);ЕСЛИ(И(ЗНАЧЕН(ТЕКСТ(ПСТР(D2;3;2);"00";))=2;ЗНАЧЕН(ТЕКСТ(ЛЕВСИМВ(D2;2);"00";))<=29);ИСТИНА;ЛОЖЬ))););ИСТИНА;ЛОЖЬ)

Не пойму почему так отображает, в выше приведенной формуле смайлики заменить на ")" без кавычек естественно.

Соответственно дата вводится без каких-либо посторонних знаков, только цифры. Формула проверяет что бы введенный диапазон был между 01.01.1970 и 31.12.2020, при вводе большей или меньшей даты – выдает ошибку, при попытке ввести 13 и т.д. месяц – выдает ошибку, при попытке ввести более 31 дня (в январе, марте, мае, июле, августе, октябре, декабре) – выдает ошибку, при попытке ввести более 30 дней (в апреле, июне, сентябре, ноябре) – выдает ошибку, при попытке ввести более 29 дней в феврале – выдает ошибку. Осталось добить проверку високосных лет. )))))

Ну и перевести это все безобразие в дату тоже не проблема, у меня в ячейке I2 идет подсчет даты от введенной в ячейку D2 плюс 2 месяца вот такой формулой:
=ЕСЛИ(ЕПУСТО(D2);""; ЕСЛИ(ДЛСТР(D2)=7; ДАТАМЕС(ДАТА(ПРАВСИМВ(D2;4);ПСТР(D2;2;2);(СЦЕПИТЬ("0";ЛЕВСИМВ(D2))));2); ДАТАМЕС(ДАТА(ПРАВСИМВ(D2;4);ПСТР(D2;3;2);ЛЕВСИМВ(D2;2));2)))

Сразу отвечу на вопрос зачем так все усложнять – просто задача стояла сделать без макросов.
03.01.2014 17:37:11
а можно чтоб при вводе числа например 5 появлялась дата не текущего а конкретного месяца или хотябы прошлого месяца текущего года, я например делаю отчёт за прошлый месяц и чтоб не вводить значения года и месяца а только день
08.01.2014 10:26:35
Просто замените в 14 строке на:
.Value = CDate(DateSerial(Year(dDate), Month(dDate)-1, Day(dDate)))
08.01.2014 12:45:55
А как сделать  так чтобы дата и время было в одной ячейке
04.02.2014 00:16:01
Принес с работы файл созданный в офисе 2007 который работает хорошо (именно с этим макросом), но дома на офисе 2013 не хочет пишет Could not load an object because it is not available on this machine.(Не удалось загрузить объект, потому что он не доступен на этой машине.) В чем проблема?
01.06.2014 14:20:11
А как сделать чтобы выводился формат часы:минуты:секунды? Или просто минуты:секунды
02.06.2014 15:52:25
Николай здравствуйте! Случайно наткнулся на ваш сайт и ... был в восторге! Спасибо огромне за Вашу проделанную работу. Но у меня вопрос к вам, а как макрос будет выглядеть если надо ввести время так:  12:15-13:45 (это в одной ячейке). Заранее спасибо.  =Будущий гуру Exel=
P.S. и год как убрать?
09.06.2014 12:26:24
Дмитрий, посмотрите вот эту статью - многое станет понятнее по  датам и времени.
10.06.2014 09:30:41
Спасибо, очень помогло...
14.01.2015 03:58:14
Доброго времени суток Николай,
все сделал как у Вас написано, работает все прекрасно, вот только при повторном вводе даты в ячейки где уже были введены описанным Вами способом даты, выводится как ответ совершенно другая дата, т.к. из-за присвоенного автоматически формата "Дата", введенное значение воспринимается как код даты. Подскажите пожалуйста как это исправить.

Заранее благодарю
31.01.2015 14:27:39
Добрый день.

Подскажите пожалуйста, если мне нужно даты во многих столбцах (B; N;O; S) начиная с 3 строки указывать, как это прописать в макросе?
27.02.2015 17:26:50
Доброго времени суток!
Прошу прощения за вопрос, в VBA новичок. К сожалению, нет времени чтобы изучить возникшую проблему, необходимо срочно сделать форму отчета кассира КМ-6 (форма типовая, скачана из альбома унифицированных форм в Консультанте). Проблема в том, что в этой форме графы "дата составления" и "время работы" представляют собой объединение нескольких ячеек и изменение диапазона указывает на неправильное событие, если я правильно поняла. Какой должен быть макрос, чтобы эта проблема была устранена?
К сожалению, не удается вставить скрин-шот для наглядности.
Заранее благодарна.
07.05.2015 16:03:56
Добрый день. Скажите пожалуйста, как можно установить автоматический ввод даты и времени в нескольких столбцах сразу (с помощью макроса, функция Range("F3:F50200")

   For Each cell In Target   'проходим по всем измененным ячейкам
If Not Intersect(cell, Range("F3:F50200") Is Nothing Then  'если изменененная ячейка попадает в диапазон A2:A100
With cell.Offset(0, 1)   'вводим в соседнюю справа ячейку дату
  .Value = Now
  .EntireColumn.AutoFit  'выполняем автоподбор ширины для столбца B, чтобы дата умещалась в ячейке
End With
End If
   Next cell
End Sub
18.05.2015 09:49:58
Excel не распознает ввод даты на английском. Подскажите, что делаю не так ? )

Если ввожу 10янв14, преобразует в дату 10.01.2014 , формат ячейки становится Custom (все ОК)
Если ввожу 10Jan14, остается в формате General
Пробовал заранее ставить формат ячейки "Date", все равно остается текст 10Jan14
14.09.2015 14:29:31
Добрый день, спасибо за сайт и за эту статью. Воспользовалась вашем макросом и вставила его в свой файл "Табель уч.раб. вр." все получилось. Этим файлом еще пользуются руководители подразделений и хотелось бы для них вставить "подсказку" (скриншот как у вас в начале статьи) как всплывающую подсказку, но не знаю как вставлять анимированные картинки, пожалуйста, помогите сделать.

19.11.2015 10:40:25
Добрый день, у некоторых пользователей при вводе данных в разные ячейки макрос выдает ошибку Compile erorr: Synteax erorr

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
Как исправить?

Выскакивать окно Visual Basic при любом редактировании значений.
Дико неудобно, при введении каждого значения приходится закрывать окно VB, дважды нажимая на кнопки.
Один пользователь даже пробовал менять дистрибутивы винды и офиса, переустанавливать – везде одно и то же.

Судя по ошибке у стоит библиотека или скрипт, который позволяет выполнять эту процедуру.

Пожалуйста, помогите исправить.
11.02.2016 22:59:24
Приветствую! Вопрос в следующем, подскажите, какой должен быть код, чтобы можно было редактировать в дальнейшем дату в введенной ячейке? Т.е я ввел 150216, он мне автоматом перевел в 15.02.2016, супер, но, если ошибся на день, пытаешься отредактировать на 14.02.2016, дата меняется на 20.04.1949 и в дальнейшем выскакивает ошибка "Run-time Error 13. Type mismatch". После этого код больше не работает и необходимо закрывать и открывать заново документ. Это первый момент.

Второй момент, если вводить дату в формате 15022016, то в ячейке отображаются ###### - бесконечное количество решеток.

Спасибо. Код золотой и очень полезен в работе, но хочется чтобы не было таких ошибок т.к будет активно использоваться в работе. Буду очень благодарен.
01.06.2016 20:48:26
Николай!  Добрый вечер!  Подскажите начинающему плиз. Мне нужно тоже самое, что и в Вашем примере, только формат не часы:минуты:секунды, а просто минуты (единицы минут, несколько):секунды. Всего 3 цифры.  Например, 3:40. Никак не получается сделать. Моя конечная цель - автоматизация оценивания учеников по 5 бальной системе по результатам забегов на физкультуре.  Может, как-то изменить макрос?
25.06.2016 12:59:32
Здравствуйте! Очень сильно стараюсь вычислить из двух результатов времени лучший, это для забегов спортсменов, т.е. 12:55:10 и 12:55:45, как определить с помощью формул в столбе №3 лучшее время (12:55:10) что бы сразу из двух результатов писался лучший???? Где то я ошибаюсь в формул, что то ставлю лишнее или на оборот не до ставляю :) За ранее Спасибо большое!!!!
21.11.2016 20:03:34
Николай, добрый день! А почему после защиты листа, на котором применяется макрос, он перестает работать? Защита с ячеек, куда нужно вводить время, снята. Появляется ошибка Run-time error '1004': Нельзя установить свойство NumberFormat класса Range. Как устранить поломку? Спасибо заранее.
23.02.2017 07:46:00
Здравствуйте, Николай. Подскажите, как правильно прописать этот макрос, чтоб он выставлял время (без даты) в определенных строках. Например E11:AI11, E12:AI12, E15:AI15, E16:AI16 и т.д.
06.05.2017 00:27:00
Ребята или Николай, кто-нибудь в общем)) Помогите
Замечательный код, но вот вопрос:
Если я хочу внедрить этот код для подобного способа ввода даты в колонку где у меня уже был изначально какой-то формат ячеек к сожалению, и на какой бы другой формат я их не менял, результата нет положительного, тогда как я могу вернуть "никакой формат ячеек" или может мне что то в коде вашем поменять можно?

ЗЫ более того, я ввожу эти 6 цифр не в саму ячейку, у меня для этого есть код, с помощью которого вылазит диалоговое окно с просьбой ввести данные, этот код ссылается на нужную ячейку где я собственно и хотел бы увидеть дату. Но опять же эти 6 цифр я ввожу в диалоговое окно, для которого написал код.

Если не понятно что я имею в виду говоря про код и диалоговое окно, то вот немного подробнее:
Я настроил свою таблицу для работы максимально удобно, чтобы не возить мышкой и кликать триста раз по ячейкам, я уже в исходном коде листа прописал несколько полезных штук, таким образом, что мне надо только в начале ввести в определенную ячейку данные, после чего запускается код и цепь разных действий в виде диалоговых окон типа:

Диалоговое окно "Введите дату" - это как раз первый запрос - и мне она нужна не текущая, а та что я сам впишу, ибо это дэдлайн проекта. Текущая дата вводится автоматом в другом месте, это я уже благодаря кстати Николаю реализовал пару-тройку лет назад, за что большое спасибо и не только за это)) В общем мне пока приходится вводить дату полностью или писать типа "9 мая" чтобы быстрее было, но мне гораздо удобнее было бы все вводить на нампаде, раз 6 цифр ввел нажал Enter, вылазит следующее диалоговое окно "введите бюджет" ну и так далее, нампад - энтер, нампад-энтер — быстро и удобно, и не надо бегать по строке по разным ячейкам глазами выискивать, тем более риск ввести не туда, а колонок много и перепутать легко.
Это я для полной картины просто написал, чтобы понимали)) а то мало ли, может если способ ввода реализуется с помощью вот таких диалоговых окон, то этот ваш код для ввода даты не годится.

Заранее благодарю всех и Николая в особенности.
06.05.2017 00:46:01
КСТАТИ! Раз у меня есть текущая дата, то может вместо ввода 6 цифр, можно было бы написать код таким образом чтобы при вводе например цифры 3 на столько увеличивалась текущая дата и вводилась бы как раз в ячейку дедлайна в виде даты? если так можно реализовать код, то это была бы просто бомба))

Например у меня есть текущая дата 6 мая в ячейке A22, вылазит диалоговое окно с просьбой ввести число на которое будет увеличена дата ячейки A22 и введена в ячейку K22 с учетом прибавления того числа что я ввел в диалоговое окно, например я ввел цифру 3 и в ячейке K22 появилась дата 9 мая, то есть код поссчитал 6+3=9 и ввел нужные данные в ячейку K22 в формате даты "9 мая" или "09.05.2017" - без разницы

Как считаете можно такое провернуть??
04.06.2018 01:23:09
Подскажите пожалуйста как в строке 7 заменить Range("A2:A10") на динамический диапазон?
If Not Intersect(Target, Range("A2:A10")) Is Nothing Then
 
30.08.2019 11:44:55
Dim Num1 As Range
Num1 = InputBox("Текст в окошке", "Название окошка")
If Not Intersect(Target, Range(Num1)) Is Nothing Then  
06.04.2022 11:17:48
А как доработать чтобы секунды можно было так же отобразить.
пробовал менять код, но не особо получается (  
11.04.2023 22:15:23
Скрипт очень хороший, большое человеческое Вам за него спасибо.
Но, я всё же решил немного его доработать, так как при вводе некоторых значений, которые не нравятся скрипту, он вылетал с ошибкой. А именно, например, при попытке ввести двухзначное число в ячейку, он сообщал об ошибке и предлагал либо нажать End, либо Debug. И после этого не важно, что нажать, скрипт переставал работать до перезапуска Excel, так как не успевала отработать функция Application.EnableEvents = True
С новыми поправками таких проблем не возникает. Делюсь:

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
 
21.05.2023 17:05:28
Возможно не совсем по теме данный пост. Но, лично я захотел использовать данный скрипт в таблицах Google. А так как там используется не VBA, а Google Apps, данный скрипт естественно там не заработал. Поэтому пришлось переписать данный скрипт на другой язык. И так как это у меня получилось, решил им с Вами поделиться:
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);
    }
  }
}
Здесь в 4 строке задаётся:
1. название листа,
2. столбцы (в моём случае это 2 столбца С - под номером 3, и E - под номером 5)
3. диапазон (в моём случае со строки 2 по строку 9999)

Если Вам нужен только один столбец, и не нужно указывать конечное значение строк, то код в этой строке можно упростить:
if(sheet.getName() === 'Лист1' && range.getColumn() === 3 && range.getRow() > 1  ) { 
В данном коде:
'Лист1' - название листа
3 - номер столбца - 'С'
>1 - начинать со второй строки (исключаем шапку таблицы)


В данном коде осталась одна недоработка:
Если ввести в ячейку дату как положено с разделителями, а не просто голые цифры, то скрипт изменит введённую дату совершенно на другую. Пока борюсь с этим моментом. Как будет решение, изменю данный пост или напишу дополнение.
21.05.2023 22:50:26
Решил не редактировать вышенаписанный код, а разместить здесь новый, чтобы наглядно было видно отличия в коде:

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);
    }
  }
}
 
Предыдущий скрипт, при вводе даты как положено с разделителями, изменял дату на совершенно другую, так как не понимал, как правильно с ней работать. Я понимал, что нужно сделать проверку на наличие разделителя между цифрами, и в случае их наличия, преждевременно остановить работу скрипта. Но, у меня ничего не получалось, пока я не вспомнил, что Google таблицы, как и MS Excel, хранит даты в виде цифр без разделителей, отсчёт которых ведётся от 01.01.1900 г., и только на экране их показывает с разделителями, как нам нужно. Поэтому, долго гуглив, нашел нужную функцию getDisplayValue(), которая помогла вытащить мне значение даты в том виде, как она отображается на экране. И уже дальше, с помощью функции includes( "." ) скрипт смог без проблем провести проверку на наличие разделителей, и прервать дальнейшие действия по преобразованию чисел в дату.

Что сделал я, ещё помимо встраивания данного скрипта в Google таблицы:
1. Нужным столбцам присвоил формат ячеек - Дата. Это позволило вводить даты с любым разделителем. В отличие от Excel таблицы понимают следующие варианты написания дат: 18,02,2021     18/02/2021    18 02 21 и на полном автомате, без всякого скрипта преобразуют указанное написание в нормальное с разделением через точки: 18.02.2021.
2. Настроил проверку данных по типу "Допустимая дата".  Это позволило вводить даты просто двойным кликом мышки в нужную ячейку. При таком клике открывается календарик, где можно выбрать желаемую дату.
P.S. Изменения настроек названия листа, и желаемых столбцов и строк, переместились в новом скрипте в строку 6.
06.01.2024 18:24:12
Спасибо что переписали код для google таблиц.
Но в вашем коде вроде нет быстрого ввода времени. Только быстрый ввод даты.
Мы перешли с Excel на Google Sheets и для табеля учёта времени это очень пригодилось бы.
22.03.2024 10:00:16
Добрый день!
Спасибо за решение, отлично работает. В моем случае стало удобнее вводить время без двуеточия.

НО есть один нюанс: если пользователь введет время по привычке с двуеточием, то код меняет время.
Подскажите пожалуйста, как "допилить" код, чтобы в случае, если пользователь внес время по привычке с двуеточием, то ничего не происходило.  
15.05.2024 09:29:12
Доброго времени суток!!!!
Долгих лет автору сего макроса!!!!!
Очень классно. У меня всё работает как нужно.
Пока не смог переделать еще и на секунды))))), но всё равно "победю".
Еще раз огромное спасибо за Ваш труд.
Наверх