Основные принципы работы с датами и временем в Excel
Видео
Как обычно, кому надо быстро - смотрим видео. Подробности и нюансы - в тексте ниже:
Как вводить даты и время в Excel
Если иметь ввиду российские региональные настройки, то Excel позволяет вводить дату очень разными способами - и понимает их все:
|
"Классическая" форма |
3.10.2006 |
|
Сокращенная форма |
3.10.06 |
|
С использованием дефисов |
3-10-6 |
|
С использованием дроби |
3/10/6 |
Внешний вид (отображение) даты в ячейке может быть очень разным (с годом или без, месяц числом или словом и т.д.) и задается через контекстное меню - правой кнопкой мыши по ячейке и далее Формат ячеек (Format Cells):
Время вводится в ячейки с использованием двоеточия. Например
16:45
По желанию можно дополнительно уточнить количество секунд - вводя их также через двоеточие:
16:45:30
И, наконец, никто не запрещает указывать дату и время сразу вместе через пробел, то есть
27.10.2012 16:45
Быстрый ввод дат и времени
Для ввода сегодняшней даты в текущую ячейку можно воспользоваться сочетанием клавиш Ctrl + Ж (или CTRL+SHIFT+4 если у вас другой системный язык по умолчанию).
Если скопировать ячейку с датой (протянуть за правый нижний угол ячейки), удерживая правую кнопку мыши, то можно выбрать - как именно копировать выделенную дату:
Если Вам часто приходится вводить различные даты в ячейки листа, то гораздо удобнее это делать с помощью всплывающего календаря:
Если нужно, чтобы в ячейке всегда была актуальная сегодняшняя дата - лучше воспользоваться функцией СЕГОДНЯ (TODAY):
Как Excel на самом деле хранит и обрабатывает даты и время
Если выделить ячейку с датой и установить для нее Общий формат (правой кнопкой по ячейке Формат ячеек - вкладка Число - Общий), то можно увидеть интересную картинку:
То есть, с точки зрения Excel, 27.10.2012 15:42 = 41209,65417
На самом деле любую дату Excel хранит и обрабатывает именно так - как число с целой и дробной частью. Целая часть числа (41209) - это количество дней, прошедших с 1 января 1900 года (взято за точку отсчета) до текущей даты. А дробная часть (0,65417), соответственно, доля от суток (1сутки = 1,0)
Из всех этих фактов следуют два чисто практических вывода:
- Во-первых, Excel не умеет работать (без дополнительных настроек) с датами ранее 1 января 1900 года. Но это мы переживем! ;)
- Во-вторых, с датами и временем в Excel возможно выполнять любые математические операции. Именно потому, что на самом деле они - числа! А вот это уже раскрывает перед пользователем массу возможностей.
Количество дней между двумя датами
Считается простым вычитанием - из конечной даты вычитаем начальную и переводим результат в Общий (General) числовой формат, чтобы показать разницу в днях:
Количество рабочих дней между двумя датами
Здесь ситуация чуть сложнее. Необходимо не учитывать субботы с воскресеньями и праздники. Для такого расчета лучше воспользоваться функцией ЧИСТРАБДНИ (NETWORKDAYS) из категории Дата и время. В качестве аргументов этой функции необходимо указать начальную и конечную даты и ячейки с датами выходных (государственных праздников, больничных дней, отпусков, отгулов и т.д.):
Примечание: Эта функция появилась в стандартном наборе функций Excel начиная с 2007 версии. В более древних версиях сначала необходимо подключить надстройку Пакета анализа. Для этого идем в меню Сервис - Надстройки (Tools - Add-Ins) и ставим галочку напротив Пакет анализа (Analisys Toolpak). После этого в Мастере функций в категории Дата и время появится необходимая нам функция ЧИСТРАБДНИ (NETWORKDAYS).
Количество полных лет, месяцев и дней между датами. Возраст в годах. Стаж.
Про то, как это правильно вычислять, лучше почитать тут.
Сдвиг даты на заданное количество дней
Поскольку одни сутки в системе отсчета даты Excel принимаются за единицу (см.выше), то для вычисления даты, отстоящей от заданной на, допустим, 20 дней, достаточно прибавить к дате это число.
Сдвиг даты на заданное количество рабочих дней
Эту операцию осуществляет функция РАБДЕНЬ (WORKDAY). Она позволяет вычислить дату, отстоящую вперед или назад относительно начальной даты на нужное количество рабочих дней (с учетом выходных суббот и воскресений и государственных праздинков). Использование этой функции полностью аналогично применению функции ЧИСТРАБДНИ (NETWORKDAYS) описанной выше.
Вычисление дня недели
Вас не в понедельник родили? Нет? Уверены? Можно легко проверить при помощи функции ДЕНЬНЕД (WEEKDAY) из категории Дата и время.
Первый аргумент этой функции - ячейка с датой, второй - тип отсчета дней недели (самый удобный - 2).
Вычисление временных интервалов
Поскольку время в Excel, как было сказано выше, такое же число, как дата, но только дробная его часть, то с временем также возможны любые математические операции, как и с датой - сложение, вычитание и т.д.
Нюанс здесь только один. Если при сложении нескольких временных интервалов сумма получилась больше 24 часов, то Excel обнулит ее и начнет суммировать опять с нуля. Чтобы этого не происходило, нужно применить к итоговой ячейке формат 37:30:55:
Ссылки по теме
- Как вычислять возраст (стаж) в полных годах-месяцах-днях
- Как сделать выпадающий календарь для быстрого ввода любой даты в любую ячейку.
- Автоматическое добавление текущей даты в ячейку при вводе данных.
- Как вычислить дату второго воскресенья февраля 2007 года и т.п.
Спасибо за ответ!
Такой вопрос... нужно в массиве производить суммирование при условии, если месяц и год меньше сегодняшнего.
{=СУММ((ПРАВСИМВОЛ(А6:S6;7)<ПРАВСИМВОЛ(A1))*(A7:S7))}
При такой формуле суммируется и текущий месяц, а мне так не надо...
Может чего подскажите....
Подскажите, как сделать, чтобы в ячейке постоянно показывалось время компьютера (обновлялось постоянно)
Спасибо
Подскажите, есть таблица.
Колонка с датой и временем, и колонка с числовым выражением чего-то. Не важно.
Нужно посчитать это кол-во не взирая на дату, сколько всего в определённый диапазон времени по всем дням.
Как это попроще сделать?
Я пытался разбить дату по колонкам рядом с таблицей, и потом анализировать. Но может есть более простое решение?
спасибо
Подскажите пожалуйста как создать правило для условного форматирования.
В таблицу вносятся даты выдачи товаров.
В ячейку записывается текущая дата выдачи.
Как сделать, чтобы по прошествии определенного времени (например 30 дней) ячейка с датой подсвечивалась цветом?
Заранее спасибо.
Помогите, пожалуйста, с такой задачей.
В таблице дано 377 рублей за период с 15.09.22 по 15.12.22, какая, формула поможет разнести по колонкам с месяцами сумму за 15 дней сентября в колонку Сентябрь, за 31 день в октябре в колонку октябрь, 30 дней в ноябре в Ноябрь 15 дней в декабре в Декабрь? При этом эта формула должна быть пригодной и для других строк, где могут быть даты, например, с 23.10.22 по 15.02.23 или с 01.09.22 по 30.09.22.
Заранее благодарна!
Excel не хочет менять формат ячейки если используются месяце в формате "22 января 2021", "26 февраля 2022", "23 марта 2023". Как только меняю на "февраль", "март", то сразу применяется нужный формат. Вопрос, как можно быстро сменить, без поиск-замены, форматы "26 января 2023", "22 января 2023", на "26 январь 2023", "22 январь 2023"?
Спасибо.
Не получается выполнить разность даты и времени.
Возможно будут идеи.
Подскажите, пожалуйста, если я с Лист1 ячейки с датой делаю ссылку на Лист2 (через = ), чтобы на нем у меня отражалась та же дата, то как сделать, чтобы на втором листе она отражалась в ДРУГОМ формате? Просто сделать "Формат-число-нужный формат" - не работает вообще. На Лист1 формат 00.00.0000, на Лист2 нужен формат 00 мая 0000г.
Дано: Лист1 с датой в формате 31.07.2023
Нужно: эта же дата на Листе2 (делаю через =), НО в формате 31 мая 2023г.
Через Формат-Число-нужный формат не работает вообще. Как еще можно сделать, чтобы одна и таже дата в одном файле, но на разных листах имела разный формат?
Обе даты стоят занимают по 1й ячейке.
Спасибо.
Cоставляю график отпуск по нашему трудовому законодательству, в котором есть такое правило: отпуск исчисляется в календарных днях, а если на период отпуска выпадает праздничный день, этот день в число дней отпуска не включаются
Имеем таблицу с ячейками по месяцам:
- дата начала отпуска
- количество календарных дней отпуска
нужно, чтобы в отдельной ячейке автоматом проставилась корректная дата окончания отпуска, если в этот интервал попадает государственный праздничный день
Например:
- дата начала отпуска : 29.04.2024
- количество календарных дней отпуска: 14
1 и 9 мая - праздничные дни, которые не должны включаться в 14 календарных дней отпуска
соответственно, дата окончания отпуска в ячейке должна проставиться - 14.05.2024
подскажите, пожалуйста, как составить такую формулу?