Разница между двумя датами в Excel 2013

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

1) исключая воскресенье и праздничные дни

2) ВРЕМЯ: пн. до пятницы = с 7 до 19 и сб = с 7 до 15

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

Например: Время начала: 06.03.2015 10:00

Время окончания: 06.04.2015 12:00

Тогда я должен уловить разницу как 13 часов (без учета рабочего времени)


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

= (SUM (NETWORKDAYS.INTL (A2, B2, {1, "1111101"}, H $ 2: H $ 10) * {12, 8}) - IF (WEEKDAY (B2) = 7,8,12))/24 + MOD (B2,1) -MOD (A2,1)

Предполагаемая дата начала /время в A2 , дата/время окончания в B2 и список выходных в H2:H10

Отформатируйте ячейку результата как [h]: mm , и если время начала — сегодня (понедельник) в 14:00, а время окончания — ровно через неделю, вы получите t результат 68:00 (5 * 12 часов в день и 1 * 8 часов в субботу)



Содержание
  1. Вычислите разницу между две даты
  2. Разница в днях
  3. Разница в неделях
  4. Разница в месяцах
  5. Разница в годах
  6. Вычислить возраст в годах, месяцах и днях
  7. 1. Используйте DATEDIF, чтобы найти общее количество лет.
  8. 2. Снова используйте DATEDIF с «ym», чтобы найти месяцы.
  9. 3. Используйте другую формулу, чтобы найти дни.
  10. 4. Необязательно: объедините три формулы в одну.
  11. Загрузите наши примеры
  12. Другие вычисления даты и времени
  13. Вычислить между сегодняшним днем ​​и другой датой
  14. Расчет рабочих дней с праздниками или без них
  15. 1. Введите дату начала и дату окончания.
  16. 2. В другой ячейке введите следующую формулу:
  17. 3. При необходимости измените 1.
  18. 4. Введите название праздничного диапазона.
  19. Вычислить прошедшее время
  20. 1. Введите время начала и время окончания.
  21. 2. Установите формат ч: мм AM/PM.
  22. 3. Вычтите два раза.
  23. 4. Установите формат ч: мм.
  24. Вычислить время, прошедшее между двумя датами и время
  25. 1. Введите две полные даты и время.
  26. 2. Установите формат 14.03.12 13:30.
  27. 3. Вычтите два.
  28. 4. Установите формат [h]: mm.
  29. См. также

Вычислите разницу между две даты

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

Предупреждение: Если Start_date равно больше, чем End_date , результат будет # ЧИСЛО!

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

В этом примере дата начала находится в ячейке 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» . «Ym» возвращает количество оставшихся месяцев после последнего полного года.

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

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

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

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

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

Загрузите наши примеры

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

Загрузить примеры расчета даты

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

Вычислить между сегодняшним днем ​​и другой датой

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

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

Расчет рабочих дней с праздниками или без них

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

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

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

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

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

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

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

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

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

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

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

Если вы создали название праздничного диапазона в поле «Перед тем, как начать» раздел выше, затем введите его в конце вот так. Если у вас нет выходных, вы можете оставить запятую и MyHolidays. Если вы используете Excel 2007, приведенный выше пример будет таким: = NETWORKDAYS (D53, E53, MyHolidays) .

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

Вычислить прошедшее время

Вы можете рассчитать прошедшее время, вычитая одно время из другого. Сначала укажите время начала в одной ячейке, а время окончания — в другой. Обязательно введите полный рабочий день, включая час, минуты, и пробел перед 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 марта/ 01 13:30.

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

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

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

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

См. также

Функция DATEDIF
Функция NETWORKDAYS.INTL
ЧИСТРАБДНИ
Другие функции даты и времени
Вычислить разницу между двумя значениями времени

Оцените статью
clickpad.ru
Добавить комментарий