Mini-ats102.ru

ООО “Мультилайн”
8 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как в Excel вычислить часы между двумя значениями времени

Описание

Чтобы рассчитать количество часов между двумя значениями времени, вы можете использовать формулу, которая вычитает время начала из времени окончания. Если время начала и окончания охватывает полночь, вам нужно будет скорректировать формулу, как описано ниже. В показанном примере формула в D5:

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

Как рассчитать дни между датами в Google Sheets

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

При этом давайте посмотрим, как найти количество дней между двумя датами в Google Sheets.

Функция МИНУС

В отличие от Excel, Google Sheets имеет функцию вычитания, которая очень удобна для расчета простых разностей дат. МИНУС — это функция вычитания листов, и, поскольку даты хранятся внутри (как целые числа, описывающие количество дней, прошедших с определенной даты в прошлом), она отлично работает для вычитания одной даты из другой. То есть до тех пор, пока даты имеют одинаковый формат. Синтаксис для MINUS: = MINUS (значение 1, значение 2).

Чтобы использовать MINUS, откройте пустую электронную таблицу Google Sheets в своем браузере. Введите (в качестве примера) «4/4/2017» и «5/15/2017» в ячейки B3 и C3.

Теперь выберите ячейку D3, куда мы поместим функцию МИНУС. Щелкните внутри панели «fx», затем введите «= MINUS (C3, B3)» и нажмите Enter. Ячейка D3 теперь вернет значение 40, как показано ниже.

гугл даты

Это означает, что между 5/5/2017 и 15.05.2017 есть 40 дней. Вы также можете найти разницу между датами, просто введя ссылки на ячейки и не заботясь о функции МИНУС.

Например, щелкните ячейку E3 и введите «= C3-B3» на функциональной панели, как показано на снимке экрана ниже. Это также вернет 40. Хотя, поскольку вы напрямую вычитаете даты без MINUS, значение в ячейке E, вероятно, будет отображаться в формате даты и будет выглядеть очень странно.

Вы можете преобразовать формат ячейки в целочисленное значение, выбрав Формат > Число а также Число,

гугл даты2

Вы также можете сначала ввести ссылки на ячейки с более ранней датой. Если вы ввели «= B3-C3» в функциональной панели, ячейка будет содержать значение -40. Это подчеркивает, что 4/4/2017 на 40 дней отстает от 15.05.2017.

Функция DATEDIF

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

Синтаксис для DATEDIF:

DATEDIF (start_date, end_date, unit). Единицей функции может быть D (дни), M (месяцы) или Y (годы).

Чтобы найти разницу между 04.04.2017 и 15.05.2017 с DATEDIF, вы должны выбрать ячейку для добавления функции (в нашем случае, F3) и ввести «= DATEDIF» на панели «fx». Затем разверните функцию с помощью скобок, которые включают ссылки на ячейки даты начала и окончания даты B3 и C3.

Единичные дни, иначе «D», также должны быть в конце функции. Таким образом, полная функция ‘= DATEDIF (B3, C3,« D »), которая возвращает значение 40, как показано ниже.

Читайте так же:
Как включить ненормативный контент в ios 12

гугл даты3

DATEDIF также будет работать, если вы введете информацию о дате непосредственно в формулу. Щелкните ячейку электронной таблицы, чтобы добавить DATEDIF, и затем введите ‘= DATEDIF (« 5/5/2017 »,« 15/15/2017 ″, «D») ’на панели« fx ».

Это вернет 40 в выбранной ячейке, как показано ниже.

гугл даты4

Функция DAY360

Google Sheets включает DAY360, который вычисляет разницу между датами для 360-дневного года. 360-дневный календарь используется в основном для финансовых таблиц, в которых могут потребоваться расчеты процентных ставок.

Синтаксис для DAYS360:

= DAYS360 (дата начала, дата окончания, [method]). [method] это необязательный индикатор, который вы можете включить в метод подсчета дней.

Чтобы использовать эту функцию в электронной таблице Google Sheets на даты 01.01.2016 и 01.01.2017, введите «01.01.2016» в ячейку B4 в качестве даты начала, а затем введите «01.01.2017» в С4 в качестве даты окончания функции.

Теперь выберите ячейку D4, введите функцию ‘= DAYS360 (B4, C4)’ в строке «fx» и нажмите Enter. Тогда ячейка D4 будет включать в себя 360 дней между выбранными датами. Обратите внимание, что единственное реальное использование этой конкретной функции — это если вы работаете с процентными ставками.

гугл даты5

Функция NETWORKDAYS

NETWORKDAYS также вычисляет количество дней между датами, но оно не совсем то же, что и остальные. Эта функция считает только дни недели, поэтому она не учитывает выходные. (Прочитайте это как «Чистые рабочие дни», а не «Сетевые дни».)

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

NETWORKDAYS (дата начала, дата окончания, [holidays]).

Вы можете добавить эту функцию в свою электронную таблицу с примерами дат 4/4/2017 и 15/15/2017, введенными в ячейки B3 и C3. Выберите ячейку, в которую нужно включить общее количество дней, и нажмите на панели «fx», чтобы вставить функцию.

Введите ‘= NETWORKDAYS (B3, C3)’ и нажмите клавишу Enter, чтобы добавить функцию в любую ячейку электронной таблицы, которую вы выбрали для нее. Ячейка NETWORKDAYS будет включать в себя общее количество 29 для количества дней между датами.

Чтобы добавить дату праздника в функцию, сначала введите «17.04.2017» в ячейку A3. Выберите ячейку NETWORKDAYS, щелкните панель валют и измените функцию, добавив в нее ссылку на ячейку A3. Таким образом, функция будет = NETWORKDAYS (B3, C3, A3), которая вернет 28 с дополнительным выходным днем, также вычтенным из общего количества дней.

гугл даты6

Другие важные функции, связанные с датой

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

  • Функция DATE преобразует указанный год, месяц и день в дату. Формат — ДАТА (год, месяц, день). Например, ДАТА (2019,12,25) возвращает «25/12/2019».
  • Функция DATEVALUE преобразует правильно отформатированную строку даты в целое число даты. Формат DATEVALUE (строка даты); строка даты может быть любой подходящей строкой, такой как «25/12/2019» или «23.01.2012 8: 5: 30».
  • Функция DAY возвращает день месяца, на который приходится конкретная дата, в числовом формате. Формат — ДЕНЬ (дата). Например, DAY («25.12.2009») возвращает 25.
  • Функция DAYS возвращает количество дней между двумя датами. Формат — ДНИ (дата окончания, дата начала). Например, DAYS («25/12/20189», «31.08.2009») возвращает 116.
  • Функция EDATE возвращает дату через определенное количество месяцев до или после указанной даты. Формат EDATE (дата начала, количество месяцев). Например, EDATE («31.08.2009», -1) возвращает «31.07.2009».
  • Функция MONTH возвращает месяц года, на который приходится конкретная дата, в числовом формате. Формат — МЕСЯЦ (дата). Например, MONTH («8/30/2019») возвращает 8.
  • Функция TODAY возвращает текущую дату в качестве значения даты. Формат — СЕГОДНЯ (). Например, на момент написания этой статьи TODAY () вернул бы «31.08.2009».
  • Функция WEEKDAY возвращает числовое значение, показывающее день недели предоставленной даты. Формат: WEEKDAY (дата, тип) и тип может быть 1, 2 или 3. Если тип равен 1, дни считаются с воскресенья, а воскресенье имеет значение 1. Если тип равен 2, дни считаются с понедельника и значение понедельника равно 1. Если тип равен 3, дни считаются с понедельника, а значение понедельника равно 0. Например, 30.04.2009 является вторником, а WEEKDAY («30.04.2009», 1) будет верните 3, в то время как WEEKDAY («30/04/2019», 2) вернет 2, а WEEKDAY («30/04/2019», 3) вернет 1.
  • Функция YEAR возвращает числовое значение, показывающее год предоставленной даты. Формат ГОД (дата). Например, YEAR («25/12/2019») вернет 2019.
Читайте так же:
Можно ли запенивать проводку в стене

Google Sheets — невероятно мощная программа, особенно для совершенно бесплатного приложения. Хотя он может быть не таким мощным, как Microsoft Excel, он может выполнять широкий спектр задач, включая эту.

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

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

Функция РАБДЕНЬ пишется так:

1 – начальная дата, ОТ КОТОРОЙ ПОЙДЕТ ОТЧЕТ. Эта дата в число добавляемых дней НЕ ВХОДИТ!

2 – количество рабочих дней. Функция найдет дату последнего дня из указанного количества

3 – праздники. Показывают в виде диапазона, прописывают в виде массива в фигурных скобках. Если в рабочем периоде праздников нет, то не указывают. Либо указывают на диапазон с ними.

Для функции РАБДЕНЬ.МЕЖД используют такую запись:

1 – начальная дата, ОТ КОТОРОЙ ПОЙДЕТ ОТЧЕТ. Эта дата в число добавляемых дней НЕ ВХОДИТ!

2 – количество рабочих дней, функция покажет дату последнего из них.

3 – выходные дни. Excel предлагает на выбор числовые коды для выходных, но можно поступить проще. Надо указать дни недели в виде строки из единиц и нулей. Первый знак будет относиться к первому дню недели (у нас это понедельник, у кого-то воскресенье). Единицами указывается выходные дни. Например, если для стандартных выходных в субботу и воскресенье используется запись – «0000011». Не забываем про кавычки!

4 – праздничные дни

Разберем такую пример. На выполнение работы по ремонту требуется согласно нормативу 15 рабочих дней. Какого числа работы должны быть закончены в Казахстане, если они начаты 22 апреля 2020 года при условии стандартных выходных или выходных по понедельникам. Праздничные дни Казахстана в мае 2020 года – 1 мая, 7 мая, 9 мая. Пи этом так как 9 мая выпало на субботу, поэтому согласно правилам и постановлению день отдыха перенесен на 8 мая.

Решения задачи следующее

datework-4

Обратите внимание, что в функции РАБДЕНЬ.МЕЖД можно вообще убрать выходные, оставив только праздники и указов для параметра «выходные дни» строку их 7 нулей. В функции РАБДЕНЬ это невозможно, в ней выходные дни используются автоматически. И еще. Результатом работы обеих функций будет значение в числовом виде. Для преобразования их в формат дат воспользуйтесь форматом даты или примените к результату функцию ТЕКСТ, указав сначала полученное значение, а затем в кавычках нужный формат. Если нужно, чтобы дата выглядела как « число.номер месяца.номер года из 4 цифр », то есть вот так – 15.05.2020 -, тогда формат будет таким «ДД.ММ.ГГГГ».

Читайте так же:
Как в хроме настроить стартовую страницу

datework-5

Для получения даты, отстоящей от указанной на заданное количество месяцев используем функцию ДАТАМЕС. Для нее надо указать начальную дату и количество месяцев, которые надо прибавит или отнять.

datework-6

В качестве варианта используется функция ДАТА. Вначале мы извлекаем из начальной даты ДЕНЬ, МЕСЯЦ и ГОД соответствующими функциями. Затем к нужному промежутку добавляем требуемое количество. И наконец, функцией ДАТА превращаем это все в дату. В функции ДАТА вначале указываем ГОД, затем МЕСЯЦ и последним ДЕНЬ.

Все вместе выглядит так:

data

Обратите внимание, что для этих функций преобразование в формат даты не требуется. Функцию ДАТА применяют и для нахождения даты через заданное количество недель. Надо только это количество умножить на 7, а результат прибавить к значению функции ДЕНЬ.

data

Способ 3: считаем рабочие дни

Чтобы посчитать именно рабочие дни за определенный период используется функция ЧИСТРАБДНИ() .

Для примера возьмем такую табличку. В столбце А у нас начало месяца или начальная дата отсчета, в В – конец месяца или отсчета. Данная функция считает рабочие дни без учета субботы и воскресенья, но в месяцах есть еще и праздники, поэтому столбец С заполним соответствующими датами.

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

Пример

Дальше ставьте «=» и пишите функцию. У меня она выглядит так:

В качестве аргументов указываем начальную дату ( А5 ), потом конечную ( В5 ). Последний аргумент – это праздники ( С5 ). Разделяем их «;» .

ЧИСТРАБДНИ()

Нажав «Enter» появится результат, в примере ячейка D5 – 21 день.

Теперь рассмотрим, если в месяце несколько праздников. Например, в январе Новый год и Рождество. Выделяем ячейку ( D6 ) и ставим в нее равно. Потом нажимаем в строке формул на букву «f» . Откроется окно «Вставка функции» . В поле «Категория» выберите «Полный алфавитный перечень» и найдите в списке нужную функцию. Жмите «ОК» .

Список функций

Дальше необходимо выбрать аргументы функции. В «Нач_дата» выбираем начальное значение ( А6 ), в «Кон_дата» – конечное ( В6 ). В последнем поле вписываем даты праздников в скобках <> и кавычках «» . Потом нажимайте «ОК» .

Аргументы

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

D6

Чтобы не прописывать праздники вручную, можно указать в соответствующем поле определенный диапазон. У меня это С6:С7 .

Диапазон

Рабочие дни посчитаются, и функция будет иметь вид:

22 дня

Теперь сделаем подсчет для последнего месяца. Вводим функцию и заполняем ее аргументы:

В феврале получилось 19 рабочих дней.

Последний месяц

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

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

Excel works!

menu

Функции ГОД, МЕСЯЦ, ДЕНЬ в Excel — это классика. Регулярно пользуюсь ими и вам советую! Если вам нужно быстро сгруппировать данные по месяцам, сделать график, какой день недели самый загруженный или в каком году были наибольшие продажи, вам необходимо знать об этих функциях. Обо всем по порядку.

Читайте так же:
Где находится корень сайта wordpress

Функции ГОД, МЕСЯЦ, ДЕНЬ в Excel

Функция ГОД в Excel

Пример. Дана таблица данных по продажам (см. выше), необходимо найти величину продаж по годам.

В отдельном столбце записываем функцию ГОД, как показано на картинке выше. У этой функции только один аргумент — дата в числовом формате (именно в числовом формате — если формула не работает проверьте верно ли задана дата). Соответственно результатом работы формулы будет номер года в числовом значении.

После чего в столбце I рассчитываем сумму по годам при помощи СУММЕСЛИ

Функция МЕСЯЦ в Excel

Функция ГОД в Excel

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

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

ГОД, МЕСЯЦ, ДЕНЬ 02

Делается это несложно при помощи автофильтра

Функция ДЕНЬ в Excel

Функция ДЕНЬ работает точно так же как и две предыдущих — она считает порядковый номер дня этой даты в месяце. Т.е. если сегодня 22.07, число 22 получаем именно функцией ДЕНЬ:

Функция ДЕНЬ в Excel

Как мне кажется, даже поинтереснее будет функция ДЕНЬНЕД. Она находит номер дня недели из числа и выводит число.

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

После чего мы можем построить график — в какой день недели было больше всего продаж.

ГОД, МЕСЯЦ, ДЕНЬ 03

Получается, что в пятницу продажники почти не работают — делаем выводы.

Для функции ДЕНЬНЕД есть небольшая хитрость — это дополнительный аргумент, если оставить это поле пустым, то дни недели будут считаться по американской системе (воскресенье первый день недели), а если поставить 2, то по европейской — к которой мы привыкли.

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

Статьи, которые будут полезны вместе с этой статьей. Функции ГОД, МЕСЯЦ, ДЕНЬ в Excel:

  • Как группировать данные по дням в сводной таблице
  • Как посчитать количество дней между датами, в том числе количество рабочих дней
  • Функция СЕГОДНЯ и все, что с ней связано

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

Как посчитать разницу между датам в MS Excel

В MS Excel есть чрезвычайно интересная функция, о которой мало кто знает. Настолько мало, что к этой функции в экселе даже не предусмотрено контекстной подсказки при вводе, хотя, как ни странно, в справке к программе она есть и описана довольно неплохо. Называется она РАЗНДАТ() или DATEDIF() и служит для автоматического расчета разницы в днях, месяцах или годах между двумя заданными датами.

Звучит не очень? На самом деле, иногда возможность быстро и точно посчитать сколько же времени прошло с какого-то события, бывает очень полезна. Сколько месяцев прошло с дня вашего рождения, сколько времени вы уже просиживаете штаны на этом месте работы, или сколько дней вы сидите на диете – да мало ли применений этой полезной функции? А самое главное, подсчет можно автоматизировать и при каждом открытии книги MS Excel получать точные данные именно для сегодняшнего дня! Звучит интересно, неправда ли?

Читайте так же:
Можно ли восстановить смс после удаления

Функция РАЗНДАТ() принимает три аргумента:

  • Начальная дата – дата с которой ведется счет
  • Конечная дата – до которой ведется счет
  • Единица измерения – дни, месяцы, годы.

Записывается это так:

=РАЗНДАТ(начальная дата;конечная дата;единица измерения)

Единицы измерения записываются как:

  • “y” – разница дат в полных годах
  • “m” – разница дат в полных месяцах
  • “d” – разница дат в полных днях
  • “yd” – разница дат в днях с начала года без учета лет
  • “md” – разница дат в днях без учета месяцев и лет
  • “ym” – разница дат в полных месяцах без учета лет

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

=РАЗНДАТ(14.07.1984;22.03.2016;”y”)

Применение функции РАЗНДАТ() на практике

Применение функции РАЗНДАТ() на практике

Обратите внимание – последний аргумент всегда заключен в кавычки.

Если же я хочу получить точный возраст, то запишу усложненную формулу:

=РАЗНДАТ(F2;G2;”y”)&” год “&РАЗНДАТ(F2;G2;”ym”)&” месяцев”

Усложняем применение РАЗНДАТ()

Усложняем применение РАЗНДАТ()

В которой функция РАЗНДАТ() вызывается сразу два раза, с разными значения, а слова “год” и “месяцев” просто пристыковываются к результату. То есть настоящая мощь функции проявляется только тогда, когда её комбинируют с другими возможностями MS Excel.

Ещё один интересный вариант – добавить в функцию ежедневно сдвигающийся относительно сегодняшней даты счетчик. К примеру, если я решу писать формулу которая высчитывает количество дней до моего отпуска в стандартном виде, это будет выглядеть примерно так:

Простой калькулятор остатка рабочих дней

Простой калькулятор остатка рабочих дней

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

Чтобы избежать это досадной мелочи, в качестве первого аргумента (сегодняшнего числа), я подставлю не ссылку на значение хранящееся в ячейке, а другую функцию. Эта функция называется СЕГОДНЯ() и её основная и единственная задача – возвращать сегодняшнюю дату.

А это уже полноценный калькулятор в MS Excel, учитывающий сегодняшнюю дату

А это уже полноценный калькулятор в MS Excel, учитывающий сегодняшнюю дату

Раз, и проблема решена – отныне, когда бы я не открыл этот лист MS Excel, функция РАЗНДАТ() всегда будет показывать мне точное значение, рассчитанное с учетом сегодняшней даты.

Другие заметки

Вывод списка дочерних страниц в GetSimple CMS

Работа с наборами страниц, обладающих различными взаимными связями, является основной при разработке веб-сайта, содержащего значительный объём контента.

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

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

dimayakovlev.ru © 2021 — Личная территория внутри всемирной паутины

Сайт создан и поддерживается мною с целью сохранения и распространения в свободном доступе опубликованных на нём материалов. Авторство разработок, текстов и изображений, принадлежит мне, если иное не указано отдельно.

При копировании материалов с сайта, не забывайте о важности обратных ссылок.

Размещение обратной индексируемой ссылки показывает интерес к содержанию сайта, что мотивирует к работе над новыми материалами.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector