Махеш может понять, как вычислить разницу между двумя датами и временем в минутах. Однако он хотел бы рассчитать разницу в минутах, но при этом исключить часы с 17:00 до 8:00, а также исключить все, что происходит между 17:00 пятницы и 8:00 понедельника. Например, если первая дата — 18.02.09 18:00, а дата окончания — 19.02.09 9:00, правильный результат должен быть 60 минут. Махеш задается вопросом, можно ли это сделать с помощью формулы.

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

Предположим, что ваша начальная дата / время находится в ячейке A1, а конечная дата / время — в ячейке B1. Учитывая это, вы можете использовать следующую формулу:

=(NETWORKDAYS(A1,B1)-1)*("17:00"-"08:00")

+IF(NETWORKDAYS(B1,B1),MEDIAN(MOD(B1,1),"17:00"

,"08:00"),"17:00")-MEDIAN(NETWORKDAYS(A1,A1)

* MOD(A1,1),"17:00","08:00")

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

=((NETWORKDAYS(A1,B1)-1)*("17:00"-"08:00")

+IF(NETWORKDAYS(B1,B1),MEDIAN(MOD(B1,1),"17:00"

,"08:00"),"17:00")-MEDIAN(NETWORKDAYS(A1,A1)

MOD(A1,1),"17:00","08:00"))1440

Изменение (умножение исходного результата на 1440) приводит к количеству минут, а не истекшему времени. Значение 1440 получается путем умножения 60 на 24, чтобы получить количество минут в день.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (5399) относится к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и более поздних версий) здесь:

link: / excelribbon-Calculating_Elapsed_Time_with_Excluded_Periods [Расчет прошедшего времени с исключенными периодами].