Основные принципы работы с датами и временем в Excel

Видео

Как обычно, кому надо быстро - смотрим видео. Подробности и нюансы - в тексте ниже:

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

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

   "Классическая" форма

  3.10.2006

   Сокращенная форма

3.10.06

   С использованием дефисов

3-10-6

   С использованием дроби

   3/10/6

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

date1.png

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

16:45

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

16:45:30

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

27.10.2012 16:45

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

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

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

date2.png

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

datepicker.jpg

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

date3.png

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

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

date4.png

 

То есть, с точки зрения Excel, 27.10.2012 15:42 = 41209,65417

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

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

  • Во-первых, Excel не умеет работать (без дополнительных настроек) с датами ранее 1 января 1900 года. Но это мы переживем!  ;)
  • Во-вторых, с датами и временем в Excel возможно выполнять любые математические операции. Именно потому, что на самом деле они - числа! А вот это уже раскрывает перед пользователем массу возможностей.

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

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

date5.png

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

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

date6.png

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

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

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

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

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

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

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

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

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

date7.png

Первый аргумент этой функции - ячейка с датой, второй - тип отсчета дней недели (самый удобный - 2).  

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

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

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

date8.png

Ссылки по теме

 



Страницы: 1  2  3  
25.06.2016 13:23:02
Здравствуйте Николай! Я не мастер спорта в эксель как вы и по этому очень сильно стараюсь вычислить из двух результатов времени лучший, это для забегов спортсменов, т.е. 12:55:10 и 12:55:45 и т.д., как определить с помощью формулы в столбе №3 лучшее время (12:55:10) что бы сразу из двух или более результатов писался лучший???? Где то я ошибаюсь в формулах, что то ставлю лишнее или на оборот не до ставляю За ранее, спасибо большое Вам!!!!
26.06.2016 09:24:01
Олег, это несложно - используйте функцию МИН :)
26.06.2016 10:22:37
Спасибо большое!!! я её и использовал, вот только что то где ошибаюсь, так как мне надо по строчкам (на каждый забег), а не по колонкам или столбцам:) Спасибо большое еще раз!!! буду побывать :)
13.07.2016 17:18:31
Здраствуйте. Очень интересует вопрос можно ли вставить текущую динамическую дату с часами? Ну или хотя бы саму дату чтобы она автоматически каждый день менялась. Просто есть таблица и в ней строки выполнения (с временем). И вот хотелось бы при помощи условного форматирования сделать так, чтобы цвет в ячейке менялся по мере приближения этих строков. Но для этого нужна текущая дата, чтобы от нее отталкиваться. Но постоянно самому ее вводить неудобно. А так зашел бы в таблицу и видешь зеленые, орынжевые и красные ячейки. Удобно) Можно такое сделать?
11.08.2016 12:02:43
Николай,
хочу поблагодарить Вас за очень полезный ресурс и Ваше ПО - PLEX к сожалению пока не использую на 100%, но всё равно потраченных денег он стоит (скрытая реклама :D).

Хотел Вас попросить рассмотреть такой вопрос:
есть дата (сегодня, текущая), на эту дату есть дней просрочки
как прописать формулу вычисления даты когда просрочка наступила
дата сегодня - дни - дата
11.08.2016 - 2 - 09.08.2016
22.08.2016 11:12:31
Здравствуйте!
Такая проблема, в ячейке С стоит разница между большей датой в ячейке В и меньшей датой в ячейке А (С=В-А= результат например 25 лет), когда я задаю условное форматирование ячейки Д (в которой значение числовое, а не дата, например 42) по принципу (если Д<=С то форматируем, иначе нет), то ячейка Д всё равно форматируется! Подозреваю что всё дело в разности форматов.. но как заставить эксель понять что 25 лет это не 9 тысяч с чем-то, а именно 25. Прошу вашей помощи.
17.09.2016 14:11:38
Имею дату-время в таком виде 10.09.2016  19:25:00 (между датой и временем 2 пробела).
Мне нужно разделить дату и время по разным ячейкам. При этом важно не визуально разделить, используя формат ячейки, а иметь значение даты и времени поотдельности.
18.10.2016 23:08:46
Добрый день!Подскажите, пожалуйста,как в формуле указать конечную дату расчета? К примеру есть формула =ДАТА(ГОД(C14);МЕСЯЦ(C14)+1;ДЕНЬ(20)), но мне нужно чтобы формула применялась только до определенной даты.Спасибо
Подскажите пожалуйста. Есть большой лист с операциями покупки по разным видам товара и в разное время , вплоть до секунды.
Я сделала сводную таблицу по видам товаров, как можно добавить дополнительное поле по времени и чтобы оно группировалось по часам? спасибо)):)
20.11.2016 23:34:33
Здравствуйте!
Подскажите, пожалуйста, как решить следующую задачу. В первой колонке я указываю дату заключения договора, во второй количество дней до его оплаты, в третьей мне необходимо получить дату оплаты. Все было бы просто, но кроме праздничных дней мне нужно учесть переносы рабочих дней на выходные. То есть в очередной раз, когда праздник попадает на воскресенье, понедельник считается выходным, а вот следующая суббота - рабочая. Оплата договора может выпасть на эту самую рабочую субботу или человек может подписать договор в рабочую субботу. Функция РАБДЕНЬ.МЕЖД не подходит, поскольку такая рабочая суббота выпадает иногда раз в год, а в остальное время выходные стандартные. Буду очень благодарна за помощь!
22.05.2017 15:48:35
Здравствуйте, помогите пожалуйста в следующем вопросе:
Есть список ФИО, дата начала отсутствия, дата окончания отсутствия. Отсутствие может быть от 2 и более месяцев. Нужно просчитать количество рабочих дней отсутствий по месяцам:

Начало

Конец

Рабочих дней

январь

февраль

март

апрель

май

июнь

июль

август

сентябрь

октябрь

ноябрь

декабрь

Иванов Иван Иванович

04.09.2017

25.10.2017

38
Формулу нужно будет применить к большому списку ФИО
19.06.2018 14:28:00
Снимаю вопрос, сделала сама
24.05.2017 13:58:33
Скажите пожалуйста , как выполнить это задание: С помощью соответствующих функций определите название  дня недели  9 мая 1945 года (обязательно используйте таблицу).
17.06.2017 10:01:18
Подскажите!!!
Мне нужно автоматически вставить даты. я умею вт-пт вт-пт и т.д. на весь месяц. а мне нужно вт-ср-чт, т.е. 3 даты из каждой недели.
05.08.2017 14:25:19
как сложить один час с минусом и два часа с плюсом в Excel
складываю часы (плюс с минусом или минус с плюсом)
05.08.2017 14:38:30
как сложуть час с минусом и час с плюсом
25.08.2017 07:27:50
Добрый день! У меня вот какой вопрос:
Имеется таблица (списание стоимости товаров, своего рода амор тизация) у каждой позиции свой остаток месяцев и ежемесячная стоимость, как это можно автоматизировать?
Пример: данные на 31.07
Куртка - 23 мес. - 254 руб.
Костюм 15 мес. -313 руб.
И ещё такой вопрос: почему при выгрузки данных их программы 1с бухгалтерия пропадает строка с выбором листа? (Приходится копировать таблицы и пересохранить, а это не совсем удобно(((

Заранее Огромное! Огромное спасибо!!!
30.10.2017 15:16:27
Добрый день, пожалуйста, подскажите, можно ли в одну ячейку внести значения конкретного месяца, т.е. не конкретного дня, а всех дней одного месяца. Я хочу в последующем суммировать только те оплаты, которые были проведены в определенном месяце. (месяцев будет много, поэтому нужно автоматизировать). Спасибо!
17.12.2017 18:23:13
Как сделать так чтобы  в  ячейке автоматически изменялся дата
17.12.2017 20:39:55
Используйте функцию СЕГОДНЯ (TODAY)
10.01.2018 22:26:32
Здравствуйте, уважаемый Николай! Имеется значение ячейки: Текущий месяц 1. Прошу Вас подсказать как возможно реализовать чтобы в первый день следующего месяца (февраль) ячейка автоматически меняла значение на текущий месяц 2. Спасибо огромное за помощь!
11.01.2018 14:56:35
Добрый день. Подскажите пожалуйста, у меня есть начальная дата с точностью до минуты например 13.12.17 12:01 и конечная дата 18.12.17 14:02, Мне необходимо посчитать сколько целых рабочих дней попало в данный промежуток времени (за исключением суббот, воскресений и праздников если таковые попадают в это промежуток). С помощью какой функции это возможно посчитать?
18.02.2019 21:05:21
Артём, попробуйте использовать функцию ЧИСТРАБДНИ (NETWORKDAYS)
13.05.2018 00:44:02
Здравствуйте!
Подскажите как сделать так, чтобы формула могла складывать дату с временем начало работы линии, где дата находится в одной ячейки а время в другой с временем (периодом например 12 часов работы линии) и выдает ответ где будет фиксироваться дата в одной ячейки а время соответственно тоже в другой!
26.01.2019 22:21:54
Добрый вечер. Подскажите, как в функцию ЕСЛИ вложить период между датами. Например, если сегодняшняя дата входит в этот период, -1, если нет, то 0
18.02.2019 21:03:41
Ну, например, так:
=ЕСЛИ(И(СЕГОДНЯ()>=дата_начала; СЕГОДНЯ()<=дата_окончания);-1;0)
18.02.2019 23:53:21
Спасибо за отклик. С этим разобрался. А теперь проблема с месяцем. Мне нужно, чтобы вместо даты стоял месяц, например Март.
13.05.2019 16:45:40
Добрый день!
Помогите пожалуйста.
Мне автоматически надо сделать список дат по неделям. Типо такого:
06.05.2019-12.05.2019
13.05.2019-19.05.2019
20.05.2019-26.05.2019
27.05.2019-01.06.2019
03.06.2019-09.06.2019
10.06.2019-16.06.2019
17.06.2019-23.06.2019
24.06.2019-30.06.2019
Как сделать автоматически? Может кто знает?
30.07.2019 10:27:41
Если еще актуально, добавьте рядом два столбца, в один введите в первую строку 06.05.2019, во вторую строку 13.05.2019, аналогично во втором столбце пропишите 12.05.2019 и 19.05.2019 соответственно.

Протяните оба столбца хоть до бесконечности вниз за правый нижний угол.
В соседнюю ячейку(в моем случае A1) где вам необходимо получить результат, напишите формулу:
=СЦЕПИТЬ(ТЕКСТ(B1;"дд.ММ.гггг";);"-";ТЕКСТ(C1;"дд.ММ.гггг";))
*Важно - ММ заглавными, а не строчными, иначе будете получать вместо месяца 00
16.05.2019 12:17:18
Здравствуйте Николай ,возникла проблема при сортировке данных в сводной таблице по датам ,у меня даты прихода записаны с точными числами и после создания сводной таблицы бывает выдает по два , три ,а то и четыре одинаковых месяца в столбцах. Подскажите, пожалуйста, есть ли функция которой можно заменить все дни в датах к примеру на первое число того месяца ,который в дате(пробовал группировку ,но не удобно ,мне очень важен год, а с годом группировка добавляет еще строку, хочеться сэкономить места немного:))
16.05.2019 14:31:28
2 дня искал решение перед тем как написать комментарий, но хорошая мысля приходит апосля...
в новом столбце использовал функцию дата ,а в аргументах год и месяц через функции ГОД и МЕСЯЦ сделал ссылку на столбец с нужной датой, в аргументе день написал 1 ,вот и всё)
16.07.2019 10:35:44
Здравствуйте. как сделать так, чтобы выходные и праздники пропускались, но чтобы выходные и праздники были не более 10 дней. после этого счет дней идет подряд.
30.07.2019 10:35:31
Марсель, мало вводных данных - в каком виде имеются даты и в каком виде нужен результат?
Есть 2 даты между которыми необходимо подсчитать количество дней по заданному условию, или есть массив с датами и нужно просто пометить даты которые являются выходными после 10 выходных и т.д.?
Вариантов вашей задачи может быть огромное множество, решений - еще больше!
30.07.2019 12:43:56
Здравствуйте!

У меня вопрос по использованию функции ЕСЛИ с ячейками времени. Есть ряд ячеек с данными такого формата "0:02:15". Если вводить значения времени вручную, но общее время суммируется правильно.
А если подставлять их через поиск (формулу ЕСЛИ), то они выглядят внешне как время, но уже не являются временем и не суммируются (толкьо считается количество)

/drive.google.com/file/d/1Uiu1Oqm7j-Ao8heQWQwp2bXtg4brrwqD/view?usp=sharing

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

Благодарю!
Елена
Страницы: 1  2  3  
Наверх