Основные принципы работы с датами и временем в 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 года и т.п.
Я сделала сводную таблицу по видам товаров, как можно добавить дополнительное поле по времени и чтобы оно группировалось по часам? спасибо))
Подскажите, пожалуйста, как решить следующую задачу. В первой колонке я указываю дату заключения договора, во второй количество дней до его оплаты, в третьей мне необходимо получить дату оплаты. Все было бы просто, но кроме праздничных дней мне нужно учесть переносы рабочих дней на выходные. То есть в очередной раз, когда праздник попадает на воскресенье, понедельник считается выходным, а вот следующая суббота - рабочая. Оплата договора может выпасть на эту самую рабочую субботу или человек может подписать договор в рабочую субботу. Функция РАБДЕНЬ.МЕЖД не подходит, поскольку такая рабочая суббота выпадает иногда раз в год, а в остальное время выходные стандартные. Буду очень благодарна за помощь!
Есть список ФИО, дата начала отсутствия, дата окончания отсутствия. Отсутствие может быть от 2 и более месяцев. Нужно просчитать количество рабочих дней отсутствий по месяцам:
Начало
Конец
Рабочих дней
январь
февраль
март
апрель
май
июнь
июль
август
сентябрь
октябрь
ноябрь
декабрь
Иванов Иван Иванович
04.09.2017
25.10.2017
38
Мне нужно автоматически вставить даты. я умею вт-пт вт-пт и т.д. на весь месяц. а мне нужно вт-ср-чт, т.е. 3 даты из каждой недели.
складываю часы (плюс с минусом или минус с плюсом)
Имеется таблица (списание стоимости товаров, своего рода амор тизация) у каждой позиции свой остаток месяцев и ежемесячная стоимость, как это можно автоматизировать?
Пример: данные на 31.07
Куртка - 23 мес. - 254 руб.
Костюм 15 мес. -313 руб.
И ещё такой вопрос: почему при выгрузки данных их программы 1с бухгалтерия пропадает строка с выбором листа? (Приходится копировать таблицы и пересохранить, а это не совсем удобно(((
Заранее Огромное! Огромное спасибо!!!
Подскажите как сделать так, чтобы формула могла складывать дату с временем начало работы линии, где дата находится в одной ячейки а время в другой с временем (периодом например 12 часов работы линии) и выдает ответ где будет фиксироваться дата в одной ячейки а время соответственно тоже в другой!
=ЕСЛИ(И(СЕГОДНЯ()>=дата_начала; СЕГОДНЯ()<=дата_окончания);-1;0)
Помогите пожалуйста.
Мне автоматически надо сделать список дат по неделям. Типо такого:
Протяните оба столбца хоть до бесконечности вниз за правый нижний угол.
В соседнюю ячейку(в моем случае A1) где вам необходимо получить результат, напишите формулу:
в новом столбце использовал функцию дата ,а в аргументах год и месяц через функции ГОД и МЕСЯЦ сделал ссылку на столбец с нужной датой, в аргументе день написал 1 ,вот и всё)
Есть 2 даты между которыми необходимо подсчитать количество дней по заданному условию, или есть массив с датами и нужно просто пометить даты которые являются выходными после 10 выходных и т.д.?
Вариантов вашей задачи может быть огромное множество, решений - еще больше!
К примеру отпуск составляет 20 дней (без учета выходных и праздничных дней) но если количество выходных и праздничных составляет более 10 дней то счёт дней идёт подряд
У меня вопрос по использованию функции ЕСЛИ с ячейками времени. Есть ряд ячеек с данными такого формата "0:02:15". Если вводить значения времени вручную, но общее время суммируется правильно.
А если подставлять их через поиск (формулу ЕСЛИ), то они выглядят внешне как время, но уже не являются временем и не суммируются (толкьо считается количество)
/drive.google.com/file/d/1Uiu1Oqm7j-Ao8heQWQwp2bXtg4brrwqD/view?usp=sharing
Готова изучить любые другие формулы и приёмы)) , чтобы время подставлялось в зависимости от значения в соседней колонке, а итог считался в виде общей продолжительности времени.
Благодарю!
Елена
Спасибо за ответ!
Такой вопрос... нужно в массиве производить суммирование при условии, если месяц и год меньше сегодняшнего.
{=СУММ((ПРАВСИМВОЛ(А6:S6;7)<ПРАВСИМВОЛ(A1))*(A7:S7))}
При такой формуле суммируется и текущий месяц, а мне так не надо...
Может чего подскажите....
Подскажите, как сделать, чтобы в ячейке постоянно показывалось время компьютера (обновлялось постоянно)
Спасибо