Как записать дату и время в ячейку Excel

Временная метка в Excel — это записанная в ячейку дата или время, вы можете использовать её когда хотите что-то отследить.

К примеру: вы хотите записать когда был создан отчёт или сформированы новые цены, вариантов может быть очень много и функция крайне полезна.

Итак, начнём.

Горячие клавиши для записи даты или времени

Этот способ наиболее быстрый, если вам необходимо записать дату или время в несколько ячеек сразу(т.к. горячие клавиши можно нажимать довольно быстро).

Вот эта комбинация:

CTRL + : (удерживайте клавишу CTRL и нажмите кнопку “:”).

Вот как его использовать:

• Выделите ячейку, куда нужно записать дату;
• Нажмите горячие клавиши.

Следует знать несколько важных вещей:

• Функция «берет» время из вашей системы.

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

Не забывайте, что это время НЕ МЕНЯЕТСЯ с течением времени. Функция ставит текущую временную метку, то есть записывает текущее дату/время. Если вы откроете файл завтра, данные не изменятся.

Эта функция вставляет только время (без даты):

Control + Shift + :  

В ситуации, когда вам нужно поставить и дату, и время, вы можете поставить их в 2 ячейки рядом.

Функции СЕГОДНЯ и ТДАТА в Excel

Рассмотренный ранее способ является статическим, т.е. записанная дата не изменится никогда, пока вы её не перепишете.

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

Запись даты используя функцию СЕГОДНЯ

Для записи даты «на сегодня», напишите =СЕГОДНЯ() в ячейку, где вы хотите её видеть.

Обычно, формат ячейки автоматически изменится на «Дата», но если по каким-то причинам у вас этого не произошло, сделайте это вручную. Ниже я расскажу как.

Для этого:

• правой кнопкой мыши и выберите «Формат ячеек»;

• В диалоговом окне «Формат ячеек» выберите категорию «Дата» на вкладке «Числовые форматы»;

• Выберите нужный формат даты (или можно просто выбрать формат по умолчанию);

• Нажмите OK.

Обратите внимание, что эта функция динамична и будет пересчитываться при каждом изменении в файле.

Запись даты и времени используя функцию ТДАТА

В случае, когда необходимо записывать не только дату, но и время, есть функция ТДАТА.

И снова, по умолчанию функция автоматически присваивает ячейке формат «Дата». Но если, вдруг, этого не произошло то поменяйте формат вручную.

Для этого:

• Щелкните правой кнопкой мыши на ячейке и выберите «Формат ячеек»;

• В диалоговом окне «Формат ячеек» выберите категорию «Дата» на вкладке «Числовые форматы»;

• В поле Тип введите «ДД.ММ.ГГГГ чч:мм:сс»

• Нажмите OK.

Это обеспечит отображение в результате как даты, так и времени.

Обратите внимание, что эта формула динамична и будет изменять дату при сохранении файла или если в нем произошли изменения.

Циклы для авто-вставки даты

Недавно мне задали такой вопрос:

«Как сделать, чтобы дата перезаписывалась только тогда, когда изменяется файл? А если файл кто-то сохранил, но изменений не было, необходимо чтобы дата осталась прежней. Я хотел бы чтобы все это происходило автоматически.».

Есть вариант с горячими клавишами (как показано выше в статье). Но это не «автоматически». При использовании сочетаний клавиш вам придется каждый раз вручную записывать дату.

Для именно автоматической записи есть способ с использованием «бесконечного цикла» или еще называют «круговая ссылка».

Итак, что такое круговая ссылка в Excel.

Допустим, у вас есть значение 5 в ячейке A1 и 10 в ячейке A2.

Теперь, если вы используете формулу =A1+A2+A3 в ячейке A3, это приведет к ошибке. Вы также можете увидеть подсказку, как показано ниже:

Такое произошло потому, что вы использовали ячейку A3 в вычислениях, происходящих в A3.

Происходит ошибка, начинается бесконечный цикл, который привел бы к остановке программы Excel. Но разработчики Excel позаботились о том, чтобы при обнаружении круговой ссылки значение не вычислялось и в таком случае, катастрофы с бесконечным вычислением не будет.

Однако существует опция, с помощью которой мы попросить Excel выполнить даже бесконечный цикл какое-то количество раз, прежде чем он остановится.

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

Заметьте, что когда мы пишем что-то в столбец A, дата пишется в столбец B. Но если само значение меняется, то дата не запишется.

Вот шаги, которые необходимо выполнить чтобы сделать это:

• Перейдите в меню «Файл» -> «Параметры»;

• В диалоговом окне «Параметры Excel» выберите «Формулы»;

• В опциях «Параметры вычислений» установите флажок «Включить итеративные вычисления»;

• Перейдите в ячейку B2 и введите следующую формулу:

=ЕСЛИ(A2<>"",ЕСЛИ(B2<>"",B2,ТДАТА()),"")  

Готово!

Когда вы введете значение в столбец А, все автоматически пропишется в столбец В.

А теперь разберемся с тем, чтобы временная метка переписывалась каждый раз, когда в ячейках столбца А происходят изменения.

Для этого нужна такая функция:

=ЕСЛИ(A2<>"",ЕСЛИ(И(B2<>"",ЯЧЕЙКА("address")=АДРЕС(СТРОКА(A2),СТОЛБЕЦ(A2))),ТДАТА(),ЕСЛИ(ЯЧЕЙКА("address")<>ADDRESS(СТРОКА(A2),СТОЛБЕЦ(A2)),B2,ТДАТА())),"")  

Эта формула использует функцию ЯЧЕЙКА для получения ссылки на последнюю отредактированную ячейку, и если она совпадает с той, что находится слева от нее, то обновляет временную метку.

Примечание: Когда вы один раз включите итеративные вычисления в Excel, они будут активны до тех пор, пока вы их не отключите.

Visual Basic автоматизация записи даты и времени

Если VBA — ваш выбор, здесь вы найдете удобный способ записывать время в Excel.

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

Ниже приведен код, который будет записывать дату в столбец B всякий раз, когда в ячейках столбца A происходит изменение.

Private Sub Worksheet_Change(ByVal Target As Range) 
On Error GoTo Handler 
If Target.Column = 1 And Target.Value <> "" Then 
Application.EnableEvents = False 
Target.Offset(0, 1) = Format(Now(), "dd-mm-yyyy hh:mm:ss") 
Application.EnableEvents = True 
End If 
Handler: 
End Sub

Код проверяет, были ли какие-либо изменения в столбце А. Если да, то дата пишется в столбец B.

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

Куда поместить этот код?

Этот код нужно ввести в качестве события изменения таблицы, чтобы он срабатывал при каждом изменении.

Для этого:

• Щелкните правой кнопкой мыши по имени листа и выберите «Просмотреть код» (или используйте сочетание клавиш Alt + F11, а затем дважды щелкните по имени листа);

• Скопируйте и вставьте этот код в окно;

• Закройте редактор VB.

Обязательно сохраните файл с расширением .XLS или .XLSM, так как он содержит Excel-макрос.

Создаем собственную функцию для записи даты и времени

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

После создания этой функции, можно будет использовать её как любую другую функцию Excel.

Вот код, который создает пользовательскую функцию «Временная метка» в Excel:

Function Timestamp(Reference As Range) 
 If Reference.Value <> "" Then 
 Timestamp = Format(Now, "dd-mm-yyy hh:mm:ss") 
  Else 
  Timestamp = "" 
 End If 
End Function  

Куда поместить этот код?

Этот код необходимо вставить в модуль в редакторе VB. После этого функция Timestamp(так мы её назвали) будет доступна в Excel.

Как разместить этот код в модуле:

• Нажмите ALT + F11 на клавиатуре. Откроется редактор VB Editor;

• В проводнике проекта в VB Editor щелкните правой кнопкой мыши на любом из объектов и выберите «Insert» -> «Module». Откроется окно вставки нового модуля;

• Скопируйте и вставьте приведенный выше код в окно модуля;

• Закройте редактор VB или снова нажмите ALT + F11.

Теперь вы можете использовать функцию в рабочем листе. Она проверит ячейку слева от себя и как только в ячейке появится какое-либо значение, либо уже имеющееся значение будет изменено, сразу поставит временную метку.

Убедитесь, что вы сохранили файл Excel с расширением .XLS или .XLSM, так как он содержит код VB.

Надеюсь, вам помогло данное руководство!