Основные принципы работы с датами и временем в 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 года и т.п.
Примерно так: А1=8:00, тогда A2=A1+(1/24) = 9:00
Нужно задать дату для спортивной статистики. Например футбольный, хоккейный сезоны переходные, которые проходят в формате осень-весна, например 2014-2015 гг. В одной ячейке конечно же. Ячейка с данными вроде 2014-2015, 2014/2015 как Вы понимаете не подходят для работы с ячейкой (сортировка и т.п.). Спасибо большое.
Например, ввожу 24.11.15, а отражается как "24.11, вт", ну или "вт 24 ноя.15".
Часто с этим сталкиваюсь, но каждый раз приходится подтягивать день недели через какую-нибудь формулу.
А просто форматом ячейки можно?
если ввести формат ячейки ДДД-Д.ММ.ГГ получится как раз то, что нужно:
Спасибо за статью!
Подскажите, пожалуйста, как решить такую задачу:
Существует ряд числовых значений в одной строке вперемешку с датами и текстом - как посчитать сумму только числовых значений.
Ряд может быть очень длинным, а порядок чисел, дат и текста - непредсказуемым. Заранее большое спасибо за ответ.
Задача:
Необходимо вывод необходимого времени (например: на перемещение из точки А в точку Б) в формате: 1день 23:13:16
И возможность изменения уменьшения (увеличения) этого времени на ХХ% ????
Буду очень благодарен за помощь!
Спасибо!
Набираю в гугле любую проблему практически по excel захожу сразу на ваш сайт и получаю ответ не только на текущий вопрос, но и сразу на все возможные вытекающие
Помогите, пожалуйста, Есть несколько дат: 01.01.15, 20.01.15, 29.01.15, 01.02.15, 18.02.15. Мне необходимо получить дату 5 число следующего за указанной датой месяце.
Т.е. 01.01.15 - 05.02.15
20.01.15 - 05.02.15
29.01.15 - 05.02.15
01.02.15 - 05.03.15
18.02.15 - 05.03.15
=ДАТА(ГОД(A1);МЕСЯЦ(A1)+1;5)
помогите, пожалуйста,решить такую проблему:
есть начальная дата (02.02.2016)
необходимо к ней прибавить 15 раб дней. Это я сделала при помощи функции "рабдень"
получается конечная дата 23.02.2016 (вторник)
Можно использовать какую-нибудь формулу, чтобы конечной датой устанавливался ближайший четверг после прибавления 15 р/д?
как в моем случае 25.02.2016
Помогите пожалуйста представить данные в ячейке 255833 (секунд) в виде___суток, ____ часов, ____ минут, _____секунд.
Спасибо Вам огромное!
Подскажите пожалуйста как можно зафиксировать формат даты, что бы человек кто будет прописывать дату события, не смог записть в своем стиле! Например в эксель стоит формат даты 03.03.2015, а человек захочет ввести дату по другому например 03,03,2015- и эксель это позволит сделать, но можно ли защитить от данных действий ячейку?
Премного Благодарен за внимание!
Спасибо!
хочу поблагодарить Вас за очень полезный ресурс и Ваше ПО - PLEX к сожалению пока не использую на 100%, но всё равно потраченных денег он стоит (скрытая реклама
Хотел Вас попросить рассмотреть такой вопрос:
есть дата (сегодня, текущая), на эту дату есть дней просрочки
как прописать формулу вычисления даты когда просрочка наступила
дата сегодня - дни - дата
11.08.2016 - 2 - 09.08.2016
Такая проблема, в ячейке С стоит разница между большей датой в ячейке В и меньшей датой в ячейке А (С=В-А= результат например 25 лет), когда я задаю условное форматирование ячейки Д (в которой значение числовое, а не дата, например 42) по принципу (если Д<=С то форматируем, иначе нет), то ячейка Д всё равно форматируется! Подозреваю что всё дело в разности форматов.. но как заставить эксель понять что 25 лет это не 9 тысяч с чем-то, а именно 25. Прошу вашей помощи.
Мне нужно разделить дату и время по разным ячейкам. При этом важно не визуально разделить, используя формат ячейки, а иметь значение даты и времени поотдельности.