Функции даты и времени.

Как вводить даты и время в Excel

Если иметь ввиду российские региональные настройки, то Excel позволяет вводить дату очень разными способами - и понимает их все:

Внешний вид (отображение) даты в ячейке может быть очень разным (с годом или без, месяц числом или словом и т.д.) и задается в меню Формат - Ячейки (Format - Cells) :

Время вводится в ячейки с использованием двоеточия. Например

16:45

По желанию можно дополнительно уточнить количество секунд - вводя их также через двоеточие:

16:45:30

И, наконец, никто не запрещает указывать дату и время вместе - через пробел:

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

Для ввода сегодняшней даты в текущую ячейку можно воспользоваться сочетанием клавиш Ctrl + Ж .

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

Если Вам часто приходится вводить различные даты в ячейки листа - почитайте это .

Если нужно, чтобы в ячейке всегда была актуальная сегодняшняя дата - лучше воспользоваться функцией СЕГОДНЯ (меню Вставка - Функция , категория Дата и время ):

Как Excel хранит и обрабатывает даты и время

Если выделить ячейку с датой и установить для нее Общий формат (меню Формат - Ячейки - вкладка Число - Общий ), то можно увидеть интересную картинку:

Вот так, например, выглядит 8.10.2006 15:42.

На самом деле любую дату Excel хранит и обрабатывает именно так - как число с целой и дробной частью. Целая часть числа (38998) - это количество дней, прошедших с 1 января 1900 года (взято за точку отсчета) до текущей даты. А дробная часть (0,65417), соответственно, доля от суток (1сутки = 1,0)

Из всех этих фактов следуют два чисто практических вывода:

Количество дней между двумя датами

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

Количество рабочих дней между двумя датами

Здесь ситуация сложнее. Необходимо не учитывать субботы с воскресеньями и праздники. Для такого расчета лучше воспользоваться функцией ЧИСТРАБДНИ из надстройки Пакета анализа .

Сначала необходимо подключить надстройку Пакета анализа . Для этого идем в меню Сервис - Надстройки (Tools - Add-Ins) и ставим галочку напротив Пакет анализа (Analisys Toolpak) . После этого в Мастере функций в категории Дата и время появится функция ЧИСТРАБДНИ (NETWORKDAYS).

В качестве аргументов этой функции необходимо указать начальную и конечную даты и ячейки с датами выходных (праздников):

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

Про то, как это правильно вычислять, лучше почитать .

Сдвиг даты на заданное количество дней

Поскольку одни сутки в системе отсчета даты Excel принимаются за единицу (см.выше), то для вычисления даты, отстоящей от заданной на, допустим, 20 дней, достаточно прибавить к дате это число.

Сдвиг даты на заданное количество рабочих дней

Эту операцию осуществляет функция РАБДНИ (WORKDAY) из надстройки Пакет анализа . Она позволяет вычислить дату, отстоящую вперед или назад относительно начальной даты на нужное количество рабочих дней (с учетом выходных суббот и воскресений и государственных праздинков). Использование этой функции полностью аналогично применению функции ЧИСТРАБДНИ описанной выше.

Вычисление дня недели

Вас не в понедельник родили? Нет? Уверены? Можно легко проверить при помощи функции ДЕНЬНЕД (WEEKDAY) из категории Дата и время .

Другой способ: выделить ячейку с датой, открыть меню Формат - Ячейки (Format - Cells) , вкладка Число , далее - Все форматы и ввести в строку Тип новый формат ДДДД :

Вычисление временных интервалов

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

Нюанс здесь только один. Если при сложении нескольких временных интервалов сумма получилась больше 24 часов, то Excel обнулит ее и начнет суммировать опять с нуля. Чтобы этого не происходило, нужно применить к итоговой ячейке формат 37:30:55 (меню Формат - Ячейки - Число - Время )

Самый простой и быстрый способ ввести в ячейку текущую дату или время – это нажать комбинацию горячих клавиш CTRL+«;» (текущая дата) и CTRL+SHIFT+«;» (текущее время).

Гораздо эффективнее использовать функцию СЕГОДНЯ(). Ведь она не только устанавливает, но и автоматически обновляет значение ячейки каждый день без участия пользователя.

Как поставить текущую дату в Excel

Чтобы вставить текущую дату в Excel воспользуйтесь функцией СЕГОДНЯ(). Для этого выберите инструмент «Формулы»-«Дата и время»-«СЕГОДНЯ». Данная функция не имеет аргументов, поэтому вы можете просто ввести в ячейку: «=СЕГОДНЯ()» и нажать ВВОД.

Текущая дата в ячейке:

Если же необходимо чтобы в ячейке автоматически обновлялось значение не только текущей даты, но и времени тогда лучше использовать функцию «=ТДАТА()».

Текущая дата и время в ячейке.

Как установить текущую дату в Excel на колонтитулах

Вставка текущей даты в Excel реализуется несколькими способами:

1. Задав параметры колонтитулов. Преимущество данного способа в том, что текущая дата и время проставляются сразу на все страницы одновременно.
2. Используя функцию СЕГОДНЯ().
3. Используя комбинацию горячих клавиш CTRL+; – для установки текущей даты и CTRL+SHIFT+; – для установки текущего времени. Недостаток – в данном способе не будет автоматически обновляться значение ячейки на текущие показатели, при открытии документа. Но в некоторых случаях данных недостаток является преимуществом.
4. С помощью VBA макросов используя в коде программы функции: Date();Time();Now() .

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

Чтобы сделать текущую дату в Excel и нумерацию страниц с помощью колонтитулов сделайте так:

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

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

Функция ДАТА возвращает порядковый номер определенной даты.

Синтаксис : ДАТА(год;месяц;день)

Аргументы функции ДАТА описаны ниже.

Год - обязательный аргумент. Значение аргумента год может содержать от одной до четырех цифр. Excel интерпретирует аргумент год в соответствии с используемой системой дат, используемой на вашем компьютере. По умолчанию в Microsoft Excel для Windows используется система дат 1900, то есть первой датой считается 1 января 1900 г.

Совет: Во избежание непредвиденных результатов используйте в качестве значения аргумента год четыре цифры. Например, значение "07" может интерпретироваться как "1907" или "2007". Четырехзначное обозначение года предотвращает возникновение путаницы.

• Если аргумент год находится в диапазоне от 0 (нуль) до 1899 (включительно), Excel для вычисления года прибавляет это значение к числу 1900. Например, функция ДАТА(108;1;2) возвращает 2 января 2008 (1900+108) года.

  Если аргумент год находится в диапазоне от 1900 до 9999 (включительно), Excel использует это значение как год. Например, функция ДАТА(2008;1;2) возвращает 2 января 2008 года.

  Если значение аргумента год меньше 0 или больше 9999, Microsoft Excel возвращает значение ошибки #ЧИСЛО!.

Месяц - обязательный аргумент. Положительное или отрицательное целое число в диапазоне от 1 (январь) до 12 (декабрь), представляющее месяц года.

• Если значение аргумента месяц больше 12, аргумент месяц добавляет число месяцев к первому месяцу указанного года. Например, функция ДАТА(2008;14;2) возвращает число, соответствующее 2 февраля 2009 года.

  Если значение аргумента месяц меньше 1, аргумент месяц вычитает значение этого числа месяцев, увеличенное на 1, из первого месяца указанного года. Например, функция ДАТА(2008;-3;2) возвращает число, соответствующее 2 сентября 2007 года.

День - обязательный аргумент. Положительное или отрицательное целое число в диапазоне от 1 до 31, представляющее день месяца.

• Если значение аргумента день больше числа дней в указанном месяце, аргумент день добавляет это число дней к первому дню месяца. Например, функция ДАТА(2008;1;35) возвращает число, соответствующее 4 февраля 2008 года.

  Если значение аргумента день меньше 1, аргумент день вычитает значение этого числа дней, увеличенное на 1, из первого дня указанного месяца. Например, функция ДАТА(2008;1;-15) возвращает число, соответствующее 16 декабря 2007 года.

Примечание: В Excel даты хранятся в виде порядковых номеров, что позволяет использовать их в вычислениях. Дате 1 января 1900 года соответствует номер 1, а 1 января 2008 года - 39448, так как интервал между этими датами составляет 39 447 дней. Чтобы вывести правильную дату, нужно изменить формат ячейки.

Синтаксис : ДАТА(год;месяц;день)

Например, =ДАТА(C2;A2;B2) объединяет значение года из ячейки C2, месяца из ячейки A2 и дня из ячейки B2 и помещает их в одну ячейку в виде даты. В примере ниже показан результат в ячейке D2.

Нужно вставить даты, не используя формулу? Никаких проблем! Вы можете вставить текущую или обновляемую дату . Кроме того, можно автоматически заполнить столбец датами .

Изменение формата даты

Функцию ДАТА можно использовать для формирования даты на основе даты в другой ячейке. Например, с помощью функций ГОД , МЕСЯЦ и ДЕНЬ можно создать дату годовщины, основанную на другой ячейке. Например, допустим, что Иван начал работать 10.01.2016. С помощью функции ДАТА можно рассчитать пятилетний юбилей его работы.

Функция ДАТА формирует дату.

=ДАТА(ГОД(C2)+5;МЕСЯЦ(C2);ДЕНЬ(C2))

Функция ГОД получает значение "2012" из ячейки C2.

К этому значению прибавляется пять лет ("+5"), после чего в ячейке D2 выводится "2017".

Excel хранит даты в виде чисел от 1 до 2 958 465 (в версиях ранее Excel 97 - до 65 380). Числа эти - "номер по порядку" данного дня, отсчитанный от 1 января 1900 года. Порядковое число 1 соответствует 1 января 1900 года, число 2 - это 2 января 1900 года и т.д. Дата написания этой статьи - 06 марта 2011 имеет порядковый номер 40608.
Такая система позволяет использовать даты в формулах. Например, с ее помощью легко подсчитать количество дней между двумя датами. Как это ни удивительно, но в Excel есть дата с порядковым числом, равным нулю, - 0 января 1900 года. Эта несуществующая дата используется для представления временных данных, которые не связаны с какой-либо конкретной датой.
Чтобы отобразить числовое значение в виде даты, просто примените к данной ячейке формат даты.

В Excel существуют две системы дат. Microsoft Excel поддерживает систему дат 1900 и систему дат 1904. Эти системы отличаются начальной датой, от которой отсчитываются порядковые числа дат. В системе дат 1900 начальной датой является 1 января 1900 года, а в системе 1904- 1 января 1904 года. По умолчанию Excel для Windows использует систему дат 1900, a Excel для Macintosh- систему 1904. Чтобы обеспечить совместимость с файлами электронных таблиц Macintosh, Excel для Windows может поддерживать систему дат 1904. При открытии документа, подготовленного на другой аппаратной платформе, смена системы дат происходит автоматически. Например, при открытии в Excel для Windows рабочей книги, созданной в Excel для Macintosh; система дат 1904 будет установлена автоматически. Будьте внимательны, если используете совместно рабочие книги, в которых применяются разные системы дат. При связывании данных передается порядковое число даты, которое в рабочей книге переводится в дату в соответствии с установленной в ней системой дат.

Максимальная доступная дата - 31 декабря 9999 года (в версиях до Excel 97 - 31 декабря 2078 года).

Время Excel хранит так же в виде чисел, точнее в виде десятичных дробей чисел соответствующих дат. То есть к порядковому номеру даты добавляется дробная часть, соответствующая части суток. Например 06 марта 2011 года имеет порядковый номер 40608, а полдень 06 марта 2011 года (12:00) будет соответствовать числу 40608,5 так как 12 часов - это 0,5 от 24.

Максимальное значение времени, которое можно ввести в ячейку (без значения даты), составляет 9999:59:59 (без одной секунды 10 тыс. часов).

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

Вставка фиксированного значения даты или времени в ячейку Excel

Фиксированное значение на листе не изменяется при пересчете или открытии листа. При нажатии клавиш CTRL+; для вставки текущей даты в ячейку Excel "делает снимок" текущей даты, который вставляется в ячейку. Это значение ячейки не изменяется, поэтому называется статическим.

Вставка обновляемых значений даты или времени

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

Для вставки обновляемой текущей даты или времени используются функции СЕГОДНЯ и ТДАТА , как показано в приведенном ниже примере. Дополнительные сведения об использовании этих функций см. в статьях Функция СЕГОДНЯ и Функция ТДАТА

Пример

Выделите текст в таблице, приведенной выше, и нажмите клавиши CTRL+C.

На пустом листе щелкните ячейку A1 и нажмите клавиши CTRL+V. При работе в Excel Online повторите копирование и вставку для каждой ячейки из примера.

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

Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите клавиши Ctrl+` (знак ударения) или на вкладке Формулы в группе Зависимости формул нажмите кнопку Показывать формулы .

Скопировав пример на пустой лист, его можно настроить в соответствии со своими потребностями.

Примечание: Функции СЕГОДНЯ и ТДАТА меняют свои значения только при расчете листа или при выполнении макроса, содержащего эту функцию. Непрерывного их обновления не происходит. В качестве значений даты и времени используются показания системных часов компьютера.

Изменение формата даты и времени

Чтобы изменить формат даты или времени, щелкните ячейку правой кнопкой мыши и выберите Формат ячеек . Затем в диалоговом окне Формат ячеек на вкладке Число в группе Числовые форматы , нажмите Дата или Время и в списке Тип выберите тип, после чего нажмите ОК .