Как разделить дату на день, месяц, год в Excel

Обновлено: 21.11.2024

Если ячейка содержит объединенные дату и время, используйте формулу, чтобы добавить дату или время в отдельные столбцы. Или разделите дату и время без формул — используйте функцию быстрого заполнения Excel.

Разделить значения даты и времени

Если вы загружаете данные в Excel, один столбец может иметь комбинированное значение даты и времени. В этом коротком видео показано, как быстро разделить это значение на отдельные столбцы даты и времени.

В первом примере используется простая формула, а во втором примере формула не используется — достаточно просто ввести ее. Письменная инструкция находится под видео. Стенограмма видео находится в конце этой страницы

Получить значение даты с помощью формулы

Если ячейка содержит объединенные дату и время, вы можете извлечь только значение даты, используя функцию INT. Excel хранит даты в виде чисел, где десятичная часть представляет время.

На снимке экрана ниже целое число в ячейке B3 (42418) представляет дату, а десятичная часть (0,50) — время.

Функция INT возвращает только целую часть этого числа, представляющую дату. В следующем примере объединенная дата и время находятся в ячейке A2.

Чтобы получить дату, введите в ячейку B2 следующую формулу: =INT(A2)

Если данные находятся в именованной таблице Excel, в формуле будут использоваться структурированные ссылки с именами полей. В приведенном ниже примере объединенная дата/время находится в столбце DateTime. Введите следующую формулу в первую строку столбца Дата (ячейка B2):

Дата: : =INT([@DateTime])

Формула должна автоматически заполняться до последней строки таблицы. После ввода формул отформатируйте эти столбцы в предпочитаемых форматах даты и времени.

Получить значение времени с помощью формулы

Если ячейка содержит объединенные дату и время, вы можете использовать функцию ЦЕЛОЕ, чтобы вывести значение времени в отдельный столбец. Даты хранятся в Excel в виде чисел, где десятичная часть представляет время.

Ниже приведены 3 формулы времени:

1. Время – час, минута, секунда

Чтобы рассчитать значение времени, вычтите целочисленное значение даты из объединенных даты и времени. Оставшаяся десятичная часть — это время.

В приведенном ниже примере объединенные дата и время находятся в ячейке A2.

Введите следующую формулу в ячейку C2, чтобы получить время с часами, минутами и секундами:

=A2 – ЦЕЛОЕ(A2)

Чтобы отформатировать время,

На вкладке "Главная" ленты Excel нажмите стрелку раскрывающегося списка для параметра "Числовое форматирование".
Выберите параметр "Время".

Формула времени в таблице Excel

Если данные находятся в именованной таблице Excel, формула времени будет использовать структурированные ссылки с именами полей. В приведенном ниже примере объединенная дата/время находится в столбце DateTime. Введите следующую формулу в первую строку столбца «Время» (ячейка C2):

Время: =[@DateTime]-INT([@DateTime])

2. Время – час, минута

На некоторых листах может потребоваться указать время только с часами и минутами, исключая секунды.

Используйте эту формулу в ячейке D2, где дата находится в ячейке A2

Функция ВРЕМЯ имеет 3 аргумента: 1 час, 2 минуты, 3 секунды

HOUR получает значение часа из ячейки A2.
MINUTE получает минуты от A2.
В качестве второго аргумента вводится ноль вместо использования значения в A2.

Чтобы отформатировать время в часах и минутах:

На вкладке "Главная" ленты Excel в правом нижнем углу группы "Число" нажмите кнопку вызова диалогового окна (или используйте сочетание клавиш Ctrl + 1 )
В диалоговом окне "Формат ячеек" выберите категорию "Время".
Выберите формат 13:30, затем нажмите "ОК".

3. Время – час

Если вы просто хотите показать час, вы можете отобразить это значение в виде числа или времени

Час как число

Чтобы получить час в виде числа, используйте следующую формулу: =ЧАС(A2)

Отформатируйте полученный результат как числовой или общий, чтобы видеть только число в ячейке.

Час как время

Чтобы получить час как время, используйте следующую формулу: =ВРЕМЯ(ЧАС(A2),0,0)

Отформатируйте результат как время, например 13:30 или 13:30

Получить значение даты или времени без формулы

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

ПРИМЕЧАНИЕ: даты и время, введенные с помощью Flash Fill, НЕ будут обновляться автоматически, если изменится комбинированная ячейка даты/времени — это статические записи.

Вот как можно использовать Flash Fill для даты или времени.

Введите первые две даты в столбце B на основе дат в столбце A.

Затем перейдите на вкладку "Данные" на ленте Excel и нажмите "Мгновенное заполнение".

Остальные даты должны заполниться, и вы можете использовать окно параметров быстрого заполнения, чтобы подтвердить или отменить изменения.

ПРИМЕЧАНИЕ. Если мгновенное заполнение не работает, нажмите вкладку «Файл» в верхней части Excel, затем нажмите «Параметры». Выберите категорию «Дополнительно» и в параметрах редактирования установите флажок «Автоматическая заливка флэш-памятью»

Проделайте то же самое, чтобы заполнить столбец «Время»: введите первые два раза, а затем используйте функцию быстрого заполнения, чтобы заполнить список.

Получить образец файла

Получите образец книги "Разделить дату и время". Заархивированный файл имеет формат xlsx и не содержит макросов.

Расшифровка видео

Как разделить дату и время в Excel

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

Разделить с помощью формул

В первом будет использоваться формула. Итак, здесь я нажму и введу знак равенства.

Мы будем использовать функцию INT, которая предназначена для целого числа и будет извлекать целое число из этой ячейки, которая является просто датой, частью даты и времени.

Когда я нажму Enter, мы увидим дату. Теперь, если вы видите число, вы можете отформатировать его как дату.

Затем я могу выбрать эту ячейку, дважды щелкнуть маркер заполнения, и я очень быстро получу все даты.

Получить значение времени

Если к дате привязано время, это десятичное число.

Поэтому, если сейчас 12 часов дня, это будет число даты 0,5.

Поэтому мы хотим просто получить эту десятичную часть, поэтому мы напечатаем равно дате. Так что это полный член, включая десятичную дробь.

Затем за вычетом части даты мы хотим вычесть это полное число.

Поэтому мы воспользуемся скобкой INT и нажмем на дату. И когда я нажимаю Enter, мы получаем время. И опять же, вам, возможно, придется отформатировать это как время, если вы видите просто число. А затем заполните это.

Разделить без формул

Мы также можем получить это без формулы. Итак, вот еще одна копия того же списка. А здесь я просто введу первые две даты.

Поэтому я наберу 01.02.16 и 03.02.16.

А теперь мы воспользуемся быстрой заливкой. И если вы делаете это с текстом, он обычно заполняется автоматически, как только мы устанавливаем шаблон, но для чисел или дат это не так.

Мы можем перейти на вкладку "Данные" и нажать "Быстрое заполнение", после чего вам будут предложены остальные даты.

И затем вы можете прийти сюда и принять или отменить или просто оставить все как есть, если вы хотите принять это.

Получить значение времени

И мы можем сделать то же самое для времени. Так что я наберу первую пару и 9:32

А теперь снова я пойду и нажму «Быстрая заливка», и она заполнит остальные, и они выглядят нормально для меня. Поэтому я оставлю их как есть.

Теперь они не изменятся, если эти даты изменятся.

На листе, где мы использовали формулу, если мы обновим время или дату, потому что у нас есть формулы, они обновятся.

В этом посте вы узнаете, как разделить дату на отдельные дни, месяцы и годы в Excel. Как быстро разделить дату на день, месяц и год, используя функции формул или текста в столбцы в Excel.

Разделить дату на день, месяц и год с помощью формул

Предполагается, что у вас есть список данных в диапазоне A1:A6, которые содержат значения даты. И вы хотите разделить каждое значение даты на три отдельных столбца для дня, месяца и года. Как этого добиться. Вы можете использовать три формулы, основанные на функции ДЕНЬ, функции месяца и функции года, чтобы получить результат разделения дат. Просто выполните следующие действия:

Эта формула извлечет значение дня из заданной даты в диапазоне A2:A6.

Эта формула извлечет значение месяца из заданной даты в диапазоне A2:A6

Вы должны увидеть, что все даты в диапазоне A2:A6 разделены на отдельные дни, месяцы и годы.

Разделить дату на день, месяц и год с помощью функции «Текст в столбец»

Вы также можете использовать параметры «Текст в столбец», чтобы получить тот же результат разделения даты на отдельные день, месяц и год в трех столбцах. Вам просто нужно сделать следующие шаги:

Видео: разбить дату на день, месяц и год

Связанные функции

Если вы когда-либо использовали MS Word или MS Powerpoint, вы должны быть знакомы с тем, что в них обоих вы можете вставить разрыв строки, просто нажав клавишу «Ввод» на клавиатуре. Мы не придаем этому значения.

Предположим, что в MS Excel у вас есть таблица, состоящая из нескольких столбцов, состоящих из нескольких значений, и вы хотите фильтровать, чтобы удалить указанные столбцы из таблицы. Вы могли бы успокоиться и предпочли бы .

Мы часто делаем сводку или статистику в конце месяца или года. В этих сводных таблицах есть по крайней мере два столбца, в одном столбце указывается дата, а в другом — продажи или продукт.

Иногда нам нужно преобразовать формат даты и времени в формат только даты в Excel, например, преобразовать 29.01.2019 06:51:03 в 29.01.2019, мы можем преобразовать формат с помощью формулы или настроек формата. Эти два способа просты в освоении, поэтому вы .

Иногда нам нужно получить информацию о годе из формата даты и времени, чтобы год отображался только в Excel. Например, конвертируя 29.01.2019 06:51:03 в 2019, мы можем получить информацию о годе по формуле или настройкам формата. Два пути.

В этом посте рассказывается, как рассчитать оставшиеся дни в заданном месяце или году в Excel. Как рассчитать количество дней, оставшихся в месяце или году, используя формулу в Excel 2013/2016. Рассчитать остаток.

В этом посте рассказывается, как разделить ячейки с разделителями-запятыми на новые строки или столбцы в Excel. Как разделить значения, разделенные запятыми, на строки с помощью функции «Текст в столбец» в Excel. Как преобразовать текст, разделенный запятыми.

В этом посте рассказывается, как сортировать даты или дни рождения по месяцам или дням только в Excel. Как сортировать даты по году, месяцу или дню только с помощью формулы в Excel 2013/2016. Как использовать .

В этом посте рассказывается, как добавить текущий месяц или год в ячейку, верхний или нижний колонтитул на листе. Как вставить текущую дату или время в ячейку с формулой в Excel. .

В этом посте рассказывается, как разделить несколько строк в ячейке на отдельные строки или столбцы в Excel. Как разделить содержимое ячейки на несколько ячеек с помощью функции «Текст в столбцы» в Excel. Разделить несколько строк.

Каждый раз, когда вы вводите дату в ячейку, Excel автоматически распознает формат и преобразует ячейку в ячейку даты.

Таким образом, Excel знает, какая часть введенной вами даты является месяцем, какая является годом, а какая днем.< /p>

Это может быть очень полезно во многих отношениях. Одним из преимуществ этой возможности Excel является то, что он позволяет отображать дату в любом формате. Он даже позволяет извлекать части даты, которые вам нужны.

Например, вы можете обнаружить, что день в дате не имеет значения, и вам нужно просто отобразить месяц и год.

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

В этом руководстве мы рассмотрим три способа преобразования даты в месяц и год в Excel.

Оглавление

Пример данных

В этом руководстве мы будем использовать следующий набор дат. Мы будем конвертировать эти даты в месяц и год в Excel:

При работе с датами в первую очередь важно распознавать исходный формат дат Excel. Например, в американском формате даты обычно начинаются с месяца и заканчиваются годом (мм/дд/ гггг).

В Великобритании и других странах даты начинаются с дня и заканчиваются годом (дд/мм/гггг). В других странах, таких как Китай, Иран и Корея, порядок полностью изменен (гггг/мм/дд).

В зависимости от настроек даты на вашем компьютере Excel будет по-разному обрабатывать части вашей даты. Поэтому при вводе даты убедитесь, что вы проверили формат и вводите дату в правильном порядке. Вы же не хотите, чтобы дата 10 февраля считалась 2 октября, а не 10 февраля!

Преобразование даты в месяц и год с помощью функции МЕСЯЦ и ГОД

Давайте посмотрим, как можно извлечь месяц из нашего примера данных:

Нажмите на пустую ячейку, где должен отображаться месяц (B2).
Введите: =МЕСЯЦ, за которым следует открывающая скобка (.
Нажмите на первую ячейку, содержащую исходную дату (A2).
Добавить закрывающую скобку )
Нажмите клавишу возврата.
Это должно отображать месяц года, соответствующий исходной дате. Скопируйте его в остальные ячейки столбца, перетащив маркер заполнения вниз или дважды щелкнув его.

Вы увидите столбец B, заполненный месяцем года для всех дат столбца A.

Теперь давайте посмотрим, как можно извлечь год из того же набора данных:

Нажмите на пустую ячейку, где должен отображаться год (C2)
Введите: =YEAR, за которым следует открывающая скобка (.
Нажмите на первую ячейку, содержащую исходную дату (A2).
Добавить закрывающую скобку )
Нажмите клавишу возврата.
Должен отображаться год, соответствующий исходной дате. Скопируйте его в остальные ячейки столбца, перетащив маркер заполнения вниз или дважды щелкнув его.

Вы увидите столбец C, заполненный годом, соответствующим всем датам столбца A.

Теперь давайте посмотрим, как можно объединить два результата, чтобы отобразить месяц и год в удобном формате.

Допустим, вы хотите отображать месяц и год как "2-2018" для даты "10.02.2018" и хотите следовать этому шаблону для всех дат.

Нажмите на пустую ячейку, в которой вы хотите отобразить новый формат даты (D2)
Введите формулу: =B2 & "-" & C2. Можно также ввести: =МЕСЯЦ(A2) & "-" & ГОД(A2).
Нажмите клавишу возврата.
Это должно отображать исходную дату в требуемом формате. Скопируйте его в остальные ячейки столбца, перетащив маркер заполнения вниз или дважды щелкнув его.

Теперь все ваши ячейки в столбце D2 имеют новый формат:

В качестве альтернативы, если вы хотите отображать месяц и год как 2/2018, вам нужно всего лишь заменить «-» между ними на «/». Таким образом, вы можете отображать месяц и год в любом формате, который вам нравится.

Наконец, если вы хотите просто сохранить преобразованные значения и хотите удалить исходные даты и любые промежуточные столбцы, которые вы создали, вам необходимо сначала преобразовать результаты формулы в постоянные значения.

Для этого скопируйте ячейки столбца D и вставьте их как значения в тот же столбец (щелкните правой кнопкой мыши и выберите Параметры вставки->Значения во всплывающем меню). Теперь вы можете удалить столбцы от A до C. У вас останутся только преобразованные значения, содержащие месяц и год.

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

Преобразование даты в месяц и год с помощью функции ТЕКСТ

Функция ТЕКСТ в Excel преобразует любое числовое значение (например, дату, время и валюту) в текст в указанном формате.

Синтаксис функции ТЕКСТ:

= ТЕКСТ (значение, код_формата)

значение – числовое значение или ссылка на ячейку, которую вы хотите преобразовать.
format_code – это формат, в который вы хотите преобразовать ячейку.

В приведенном выше примере функция ТЕКСТ применяет код_формата, указанный вами в значении, и возвращает текстовую строку в этом формате. Например, если у вас есть дата «10.02.2018» в ячейке A2, тогда =ТЕКСТ(A2, «мм/гггг») вернет «02/2018»

Существует ряд кодов форматов, которые вы можете использовать. Мы перечислили ниже основные строительные блоки для кодов форматов:

Коды формата для года:

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

гг – двузначное представление года (например, 20 или 12)
гггг – четырехзначное представление года (например, 2020 или 2012)

Итак, если вы примените =TEXT(A2, yy) в нашем примере набора данных, он вернет «18».

Если вы примените =TEXT(A2, yyyy), будет возвращено «2018».

Коды формата для месяца года:

Вы можете использовать следующие четыре основных кода формата для представления значений месяца:

m – одно- или двухзначное представление месяца (например, 8 или 12).
mm – двухзначное представление месяца (например, 08 или 12).
ммм – месяц, сокращенно состоящий из трех букв (например, август или декабрь).
мммм — месяц, указанный полным названием (например, август или декабрь).

Итак, если вы примените =TEXT(A2, m) в нашем примере набора данных, он вернет «2».

Если применить =ТЕКСТ(A2, мм), будет возвращено значение "02".
Если вы примените =ТЕКСТ(A2, ммм), будет возвращено "Фев".
Если применить =ТЕКСТ(A2, мммм), будет возвращено значение «Февраль».

Давайте посмотрим, как мы можем применить функцию ТЕКСТ к нашему образцу набора данных, чтобы преобразовать все даты в разные форматы.

Сначала мы увидим, как преобразовать даты в столбце A в формат, показанный в столбце B на изображении ниже:

Ниже приведены шаги по изменению формата даты и получению только месяца и года с помощью функции ТЕКСТ:

Нажмите на пустую ячейку, в которой вы хотите отобразить новый формат даты (B2)
Введите формулу:
Нажмите клавишу возврата.
Это должно отображать исходную дату в требуемом формате. Скопируйте его в остальные ячейки столбца, перетащив маркер заполнения вниз или дважды щелкнув его.
Скопируйте результаты формулы этого столбца, нажав CTRL+C или Cmd+C (если вы работаете на Mac).
Щелкните правой кнопкой мыши столбец и в появившемся всплывающем меню нажмите «Вставить значения» в параметрах вставки.
При этом результаты формулы будут сохранены как постоянные значения в том же столбце. Теперь вы можете удалить столбец А, если хотите.

Код формата, который вы вводите в формулу (на шаге 2), будет различаться в зависимости от формата, в котором вы хотите отображать месяц и год. Вот коды формата вместе с типом результата, который вы получите при применении к нему. ячейка A2:

Функция	Результат
=ТЕКСТ(A2, «мм/гг»)< /td>	02/18
=ТЕКСТ(A2, «мм-гг»)	02-18
=ТЕКСТ(A2, «мм-гггг»)	02-2018
=ТЕКСТ(A2, «ммм , гггг")	Февраль 2018 г.
=TEXT(A2, "мммм, гггг")	Февраль 2018 г.< /td>

Итак, вы можете использовать функцию ТЕКСТ для преобразования дат в любой формат по вашему выбору. Все, что вам нужно сделать, это изменить код формата в соответствии с вашими требованиями.

Примечание. С помощью этой формулы ваша дата преобразуется в текстовый формат. Если вы хотите преобразовать его обратно в формат даты, вам нужно использовать функцию Формат ячеек.

Преобразование даты в месяц и год с помощью числового формата

Функция Excel Форматировать ячейку — это универсальная функция, которая позволяет выполнять различные типы форматирования в одном диалоговом окне.

Вот как вы можете преобразовать даты в нашем образце набора данных в другие форматы.

Выделите все ячейки, содержащие даты, которые вы хотите преобразовать (A2:A6).
Нажмите правой кнопкой мыши на выделенный элемент и выберите Форматировать ячейки в появившемся всплывающем меню. Кроме того, вы можете выбрать средство запуска диалогового окна в группе Число на вкладке Главная.
Откроется диалоговое окно Формат ячеек. Перейдите на вкладку Число
В разделе Категория в левой части поля выберите параметр Дата.
Справа отобразится несколько вариантов форматирования даты.
Выберите нужный формат. Например, если вы хотите, чтобы первая дата отображалась в формате «18 февраля», выберите соответствующий вариант формата.
Если вы не можете найти параметр для формата, который хотите использовать, вы можете использовать параметр Пользовательский в списке Категория слева. Это позволяет преобразовать ячейку в произвольный формат.
Посмотрите, доступен ли ваш формат среди кодов формата даты в разделе "Тип". Если нет, вы можете ввести код формата в поле ввода чуть ниже Тип. Итак, если вы хотите отобразить первый месяц в формате «2/18», введите «м/гг».
Нажмите "ОК", чтобы закрыть диалоговое окно Формат ячеек.

Все выбранные вами ячейки теперь должны быть отформатированы в требуемом формате.

Этот метод отличается от предыдущего (с использованием функции ТЕКСТ) четырьмя способами:

С помощью этого метода вы можете выполнять операцию непосредственно с исходными ячейками.
Вы можете выполнить конверсию за один раз. Таким образом, вам не нужно иметь отдельную ячейку, чтобы ввести формулу, затем вставить по значению, а затем удалить исходные ячейки (как это было бы необходимо при использовании функции ТЕКСТ).
Этот метод изменяет только формат исходной даты, однако базовая дата остается прежней. Поэтому, если вы хотите позже восстановить исходное значение даты (вместе с днем), вы можете легко получить к нему доступ. Однако при использовании функционального метода ТЕКСТ исходное значение даты теряется, поскольку преобразование изменяет все значение даты.
Результаты, которые вы получаете при использовании этого метода, имеют тип Дата, а не Текст, поэтому вы можете выполнять над ними операции с датами напрямую, без необходимости их преобразования.< /li>

Это три способа преобразования даты в месяц и год в Excel. Используя их, вы можете преобразовать дату в любой нужный вам формат.

Мы надеемся, что наши методы оказались вам полезными и вы примените их к своим собственным данным Excel.

Используйте функцию DATEDIF, если вы хотите вычислить разницу между двумя датами. Сначала укажите дату начала в одной ячейке и дату окончания в другой. Затем введите формулу, подобную одной из следующих.

Разница в днях

В этом примере дата начала указана в ячейке D9, а дата окончания — в ячейке E9. Формула находится в F9. «d» возвращает количество полных дней между двумя датами.

Разница в неделях

В этом примере дата начала указана в ячейке D13, а дата окончания — в ячейке E13. «d» возвращает количество дней. Но обратите внимание на /7 в конце. Это делит количество дней на 7, так как в неделе 7 дней. Обратите внимание, что этот результат также необходимо отформатировать как число. Нажмите CTRL + 1. Затем нажмите Число > Десятичные разряды: 2.

Разница в месяцах

В этом примере дата начала указана в ячейке D5, а дата окончания — в ячейке E5. В формуле «m» возвращает количество полных месяцев между двумя днями.

Разница в годах

В этом примере дата начала находится в ячейке D2, а дата окончания — в ячейке E2. «y» возвращает количество полных лет между двумя днями.

Рассчитать возраст в накопленных годах, месяцах и днях

Вы также можете рассчитать возраст или стаж работы. Результатом может быть что-то вроде «2 года, 4 месяца, 5 дней».

1. Используйте DATEDIF, чтобы найти общее количество лет.

В этом примере дата начала указана в ячейке D17, а дата окончания — в ячейке E17. В формуле «y» возвращает количество полных лет между двумя днями.

2. Используйте DATEDIF снова с «ym», чтобы найти месяцы.

В другой ячейке используйте формулу DATEDIF с параметром "ym". «Гм» возвращает количество оставшихся месяцев после последнего полного года.

3. Используйте другую формулу, чтобы найти дни.

Теперь нам нужно найти количество оставшихся дней. Мы сделаем это, написав другую формулу, показанную выше. Эта формула вычитает первый день последнего месяца (01.05.2016) из исходной даты окончания в ячейке E17 (06.05.2016). Вот как это делается: Сначала функция ДАТА создает дату 01.05.2016. Он создается с использованием года в ячейке E17 и месяца в ячейке E17. Тогда 1 представляет первый день этого месяца. Результат функции ДАТА — 01.05.2016. Затем мы вычитаем это из исходной даты окончания в ячейке E17, то есть 06.05.2016. 06.05.2016 минус 01.05.2016 равно 5 дням.

Предупреждение. Мы не рекомендуем использовать аргумент DATEDIF "md", так как это может привести к неточным результатам.

4. Необязательно: объедините три формулы в одну.

Вы можете поместить все три вычисления в одну ячейку, как в этом примере. Используйте амперсанд, кавычки и текст. Это более длинная формула для ввода, но, по крайней мере, все в одном. Совет: нажмите клавиши ALT+ВВОД, чтобы вставить в формулу разрывы строк. Это облегчает чтение. Кроме того, нажмите CTRL+SHIFT+U, если вы не видите всю формулу.

Скачать наши примеры

Вы можете загрузить пример книги со всеми примерами из этой статьи. Вы можете следовать инструкциям или создавать свои собственные формулы.

Другие расчеты даты и времени

Как вы видели выше, функция РАЗНДАТ вычисляет разницу между датой начала и датой окончания. Однако вместо того, чтобы вводить конкретные даты, вы также можете использовать функцию СЕГОДНЯ() внутри формулы. Когда вы используете функцию СЕГОДНЯ(), Excel использует в качестве даты текущую дату вашего компьютера. Имейте в виду, что это изменится, когда файл снова откроется в будущем.

Обратите внимание, что на момент написания этой статьи это было 6 октября 2016 года.

Используйте функцию ЧИСТРАБДНИ.МЕЖД, если вы хотите рассчитать количество рабочих дней между двумя датами. Вы также можете исключить выходные и праздничные дни.

Прежде чем начать: решите, хотите ли вы исключить праздничные дни. Если вы это сделаете, введите список дат праздников в отдельной области или листе. Поместите каждую дату праздника в отдельную ячейку. Затем выберите эти ячейки, выберите «Формулы» > «Определить имя». Назовите диапазон MyHolidays и нажмите OK. Затем создайте формулу, выполнив указанные ниже действия.

1. Введите дату начала и дату окончания.

В этом примере дата начала указана в ячейке D53, а дата окончания — в ячейке E53.

2. В другой ячейке введите следующую формулу:

Введите формулу, как в приведенном выше примере. 1 в формуле определяет субботу и воскресенье как выходные дни и исключает их из общего числа.

Примечание. В Excel 2007 нет функции ЧИСТРАБДНИ.МЕЖД. Однако у него есть ЧИСТРАБДНИ. Приведенный выше пример в Excel 2007 выглядел бы так: =ЧИСТРАБДНИ(D53,E53). Вы не указываете 1, потому что ЧИСТРАБДНИ предполагают, что выходные приходятся на субботу и воскресенье.

3. При необходимости измените 1.

Если суббота и воскресенье не являются вашими выходными днями, измените 1 на другое число из списка IntelliSense. Например, число 2 делает воскресенье и понедельник выходными днями.

Если вы используете Excel 2007, пропустите этот шаг. Функция ЧИСТРАБДНИ Excel 2007 всегда предполагает, что выходные приходятся на субботу и воскресенье.

4. Введите название диапазона праздников.

Если вы создали название диапазона праздников в разделе "Перед началом работы" выше, введите его в конце, как показано ниже. Если у вас нет праздников, вы можете не указывать запятую и MyHolidays. Если вы используете Excel 2007, приведенный выше пример будет таким: =ЧИСТРАБДНИ(D53,E53,MyHolidays).

Совет. Если вы не хотите ссылаться на название диапазона праздников, вы также можете вместо этого ввести диапазон, например D35:E:39. Или вы можете ввести каждый праздник внутри формулы. Например, если ваши праздники приходятся на 1 и 2 января 2016 года, введите их так: =NETWORKDAYS.INTL(D53,E53,1,). В Excel 2007 это будет выглядеть так: =ЧИСТРАБДНИ(D53,E53,)

Вы можете рассчитать прошедшее время, вычитая одно время из другого. Сначала укажите время начала в одной ячейке и время окончания в другой. Обязательно введите полное время, включая часы, минуты, и пробел перед AM или PM. Вот как:

1. Введите время начала и время окончания.

В этом примере время начала указано в ячейке D80, а время окончания — в ячейке E80. Обязательно введите часы, минуты и с пробелом перед AM или PM.

2. Установите формат ч:мм AM/PM.

Выберите обе даты и нажмите CTRL + 1 (или + 1 на Mac). Обязательно выберите «Пользовательский» > «ч:мм AM/PM», если он еще не установлен.

3. Вычтите два раза.

В другой ячейке вычтите ячейку времени начала из ячейки времени окончания.

4. Установите формат ч:мм.

Нажмите CTRL + 1 (или + 1 на Mac). Выберите «Пользовательский» > «ч:мм», чтобы результат не включал AM и PM.

Чтобы вычислить время между двумя датами и временем, вы можете просто вычесть одно из другого. Однако вы должны применить форматирование к каждой ячейке, чтобы Excel вернул нужный результат.

1. Введите две полные даты и время.

В одной ячейке введите полную дату/время начала. А в другой ячейке введите полную дату/время окончания. В каждой ячейке должны быть указаны месяц, день, год, час, минута, и пробел перед AM или PM.

2. Установите формат 14.03.12 13:30.

Выделите обе ячейки и нажмите CTRL + 1 (или + 1 на Mac). Затем выберите Дата > 14.03.12 13:30. Это не дата, которую вы установите, это просто пример того, как будет выглядеть формат. Обратите внимание, что в версиях, предшествующих Excel 2016, этот формат может иметь другую примерную дату, например 14 марта 2001 г., 13:30.

3. Вычтите два.

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

4. Установите формат [ч]:мм.

Нажмите CTRL + 1 (или + 1 на Mac). Выберите Пользовательский. В поле Тип введите [ч]:мм.

Читайте также: