Как excel хранит дату и время
Работа с датами и временем в Excel
Как вводить даты и время в Excel
Если иметь ввиду российские региональные настройки, то Excel позволяет вводить дату очень разными способами - и понимает их все:
С использованием дефисов
С использованием дроби
Внешний вид (отображение) даты в ячейке может быть очень разным (с годом или без, месяц числом или словом и т.д.) и задается в меню Формат - Ячейки ( Format - Cells) :
Время вводится в ячейки с использованием двоеточия. Например
По желанию можно дополнительно уточнить количество секунд - вводя их также через двоеточие:
И, наконец, никто не запрещает указывать дату и время вместе - через пробел:
Быстрый ввод дат и времени
Для ввода сегодняшней даты в текущую ячейку можно воспользоваться сочетанием клавиш 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 (меню Формат - Ячейки - Число - Время )
Работа с датами и временем в Excel
Как вводить даты и время в Excel
Если иметь ввиду российские региональные настройки, то Excel позволяет вводить дату очень разными способами - и понимает их все:
С использованием дефисов
С использованием дроби
Внешний вид (отображение) даты в ячейке может быть очень разным (с годом или без, месяц числом или словом и т.д.) и задается в меню Формат - Ячейки ( Format - Cells) :
Время вводится в ячейки с использованием двоеточия. Например
По желанию можно дополнительно уточнить количество секунд - вводя их также через двоеточие:
И, наконец, никто не запрещает указывать дату и время вместе - через пробел:
Быстрый ввод дат и времени
Для ввода сегодняшней даты в текущую ячейку можно воспользоваться сочетанием клавиш 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 (меню Формат - Ячейки - Число - Время )
Когда вы введите дату или время в ячейку, она отображается в формате даты и времени по умолчанию. Этот формат по умолчанию основан на региональных параметрах даты и времени, заданных на панели управления, и изменяется при их настройке на панели управления. Числа можно отобразить в нескольких других форматах даты и времени, на большинство из которых параметры панели управления не влияют.
В этой статье
Отображение чисел в качестве даты или времени
Вы можете форматирование даты и времени по мере их ввести. Например, если ввести в ячейку 2/2, Excel будет автоматически интерпретирован как дата и отобразит в ячейке 02.фев. Если это не то, что вам нужно (например, если вы хотите, чтобы в ячейке были 2 февраля 2009 г. или 02.02.09), в диалоговом окне Формат ячеек можно выбрать другой формат даты, как покажем в следующей процедуре. Аналогично, если ввести в ячейку 9:30 a или 9:30 p, Excel интерпретирует это как время и отображает 9:30 или 21:30. Вы также можете настроить способ времени в диалоговом окне Формат ячеек.
На вкладке Главная в группе Число нажмите кнопку вызова диалогового окна, расположенную рядом с надписью Число.
Вы также можете нажать CTRL+1, чтобы открыть диалоговое окно Формат ячеек.
В списке Категория выберите дата иливремя.
В списке Тип выберите нужный формат даты или времени.
Примечание: Форматы даты и времени, которые начинаются со звездочки (*), отвечают на изменения в региональных параметрах даты и времени, заданных на панели управления. На форматы без звездочки параметры, заданные на панели управления, не влияют.
Чтобы отобразить даты и время в формате других языков, выберите нужный язык в поле Языковой стандарт (расположение).
Число в активной ячейке выбранного на этом сайте отображается в поле Образец, чтобы можно было просмотреть выбранные параметры форматирования.
Создание пользовательского формата даты или времени
На вкладке Главная нажмите кнопку вызова диалогового окна рядом с именем группы Число.
Вы также можете нажать CTRL+1, чтобы открыть диалоговое окно Формат ячеек.
В поле Категория выберите дата или время ,а затем выберите числовом формате, наиболее близком по стилю к тому, который вы хотите создать. (При создании пользовательских числных форматов проще начать с существующего, чем с нуля.)
В списке Категория выберите пункт (все форматы). В поле Тип вы увидите код формата, совпадающий с форматом даты или времени, выбранным на шаге 3. Встроенный формат даты или времени нельзя изменить или удалить, поэтому не беспокойтесь о переописи.
В поле Тип введите необходимые изменения формата. Вы можете использовать любой из кодов в следующих таблицах:
Дни, месяцы и годы
Для отображения
Используйте код
Месяцев в виде чисел от 1 до 12
Месяцев в виде чисел от 01 до 12
Месяцев в виде "янв", . "дек"
Месяцев в виде "январь", . "декабрь"
Месяцев в виде первой буквы месяца
Дней в виде чисел от 1 до 31
Дней в виде чисел от 01 до 31
Дней в виде "Пн", . "Вс"
Дней в виде "понедельник", . "воскресенье"
Лет в виде чисел от 00 до 99
Лет в виде чисел от 1900 до 9999
Если вы используете "м" сразу после кода "ч" или "чч" или непосредственно перед кодом "сс", Excel отображает минуты вместо месяца.
Часы, минуты и секунды
Для отображения
Используйте код
Часы в качестве 0–23
Часы в качестве 00–23
Минуты в качестве 0–59
Минуты в качестве 00–59
Секунды в качестве 0–59
Секунды в качестве 00–59
Часы с 04:00 до 04:0
Время в 4:36:03 P
Заслон времени в часах; например, 25,02
Заслон времени в минутах; например, 63:46
За считанные секунды
AM и PM Если формат содержит am или PM, часы основаны на 12-часовом формате, где "AM" или "A" указывает время от полуночи до полудня, а "PM" или "P" — время от полудня до полуночи. В противном случае используется 24-часовой цикл. Код "м" или "мм" должен отображаться сразу после кода "ч" или "чч" или непосредственно перед кодом "сс"; в противном Excel отображается месяц, а не минуты.
Создавать пользовательские числовые форматы может быть непросто, если вы этого еще не сделали. Дополнительные сведения о создании пользовательских числных форматов см. в теме Создание и удаление пользовательских числов.
Советы для отображения дат и времени
При попытке отменить формат даты или времени с помощью выбора в списке Категория общего Excel отображает числовом коде. При повторном вводе даты или времени Excel формат даты или времени по умолчанию. Чтобы ввести определенный формат даты или времени, например январь 2010г., можно отформать его как текст, выбрав текст в списке Категория.
Чтобы быстро ввести текущую дату, выйдите из любой пустой ячейки и нажмите CTRL+; (точка с за semicolon) и при необходимости нажмите ввод. Чтобы вставить дату, которая будет обновляться до текущей даты при каждом повторном повторном пересчете или пересчете формулы, введите =СЕГОДНЯ() в пустую ячейку и нажмите ввод.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Практически любая работа, выполняемая в приложении Excel, содержит ячейки с датами или временем. Умение работать с таким типом данных, позволит Вам сэкономит много времени и сил, а также избежать разнообразных ошибок в процессе работы с программой.
К сожалению, начинающие пользователи Excel при работе с датами и временем сталкиваются с недопониманием того, как Excel их обрабатывает. Поэтому прежде чем приступать к рассмотрению это вопроса более детально, давайте разберемся с ключевыми моментами.
Представление даты в Excel
В Microsoft Excel дата представлена порядковым числом, которое представляет собой количество дней, прошедших с 0 января 1900 года. Эта несуществующая дата вполне условна и используется в Excel лишь как точка отсчета. Порядковое число 1 соответствует 1 января 1900 года, число 2 – 2 января 1900 года и т.д. Максимальная дата, которую поддерживает Excel – это 31 декабря 9999 года, что соответствует порядковому числу 2958465.
Такой способ представления позволяет использовать даты в формулах Excel. Например, для того чтобы посчитать количество дней между двумя датами, достаточно из конечной даты вычесть начальную. Одним словом, работая с датами, Excel оперирует только числами, представляя нам результат в определенных числовых форматах.
На рисунке ниже в ячейках B2 и B3 представлены две даты, а в ячейках C2 и C3 те же самые даты представлены в формате Числовой. Думаю, Вы уже догадались, что это их порядковые номера.
Чтобы посчитать количество дней между двумя датами, достаточно из конечной даты вычесть начальную, т.е. воспользоваться формулой: =B3-B2. Введя эту формулу в ячейку B4, получим следующий результат:
Отметим, что к ячейке B4 был применен формат отличный от Даты, поэтому значение представлено в днях. В противном случае, мы получили бы другую картину:
Чтобы отобразить порядковое число, соответствующее дате, необходимо применить к ячейке числовой формат отличный от формата Даты. А чтобы представить числовое значение в ячейке в виде даты, необходимо применить к этой ячейке формат Дата.
Представление времени в Excel
Время в Excel трактуется как дробная часть дня. Если говорить другими словами, то Excel принимает сутки за единицу, а все величины меньше суток (часы, минуты, секунды) – рассматривает как часть единицы. Например, 1 час – это 1/24 дня, 1 минута – это 1/1140 дня, а 1 секунда – это 1/86400 дня. Самая маленькая единица измерения времени в Microsoft Excel – одна тысячная доля секунды, т.е. 1 миллисекунда.
Как и в случае с датами, такой подход позволяет использовать значения времени в формулах. Единственным недостатком является то, что в качестве результата мы, как правило, получаем дробную часть суток, а не количество дней (как в случае с обработкой дат). Что не всегда удобно.
На рисунке ниже в столбце B представлены значения времени в формате Время, а в столбце C соответствующие им доли суток в формате Числовой.
Если в ячейку B4 ввести формулу =B3-B2 и нажать Enter, то получим следующий результат:
Поскольку ячейка B4 изначально имела формат Общий, то после ввода формулы Excel автоматически поменял формат Общий на Время. Если же применить к данной ячейке формат Числовой, то результат будет таким:
Работая со временем, собственно, как и с датами, Excel оперирует только числами. Мы же в результате видим значение, представленное в формате времени.
В этом уроке Вы узнали о том, как Microsoft Excel хранит и обрабатывает временные данные. Как видите, в этом есть своя специфика и огромное преимущество, которое позволяет Excel так легко и просто совершать с датами и временем различные математические действия. В следующем уроке Вы узнаете, как вводить и форматировать даты и время в Excel. Всего Вам доброго и успехов в изучении Excel!
Читайте также: