Вычисление разницы между 2 разами, исключая выходные, затем усреднение результатов

Question

У меня есть лист Excel 2007, в котором мне нужно рассчитать разницу между 2 разами, исключая выходные дни, а затем в конце недели или месяца, чтобы иметь возможность найти среднее значение времени в столбце «разница».

Вот что у меня так далеко:

=NETWORKDAYS(A3,H3)-1&" DAYS, "&TEXT(H3-A3,"HH"" HRS AND ""MM""MIN"""))

Что дает мне результат 1 дней, 02 часов и 03 минут, что хорошо; Я просто не могу найти способ найти среднее значение данных в этом столбце за время, выраженное в этом формате.

Какие-либо предложения?

Большое спасибо.

Answer 1

Ваша существующая формула не всегда будет работать, например, если A3 - четверг в 10:00, а H3 - следующий день в 09:00, результат не должен быть

0 DAYS, 23 HRS AND 00MIN

но фактический результат с этой формулой слишком высок, т.е.

1 DAYS, 23 HRS AND 00MIN

Почему бы не вернуть результат в формате чч: мм, а затем вы можете усреднить их, т.е. использовать эту формулу

=NETWORKDAYS(A3,H3)-1+MOD(H3,1)-MOD(A3,1)

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

Примечание: формула работает, предполагая, что A3 и / или H3 всегда являются рабочими днями

Альтернативное решение:

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

=NETWORKDAYS(A3,H3)-1-(MOD(H3,1)< MOD(A3,1))&" DAYS, "&TEXT(H3-A3,"HH"" HRS AND ""MM"" MIN""")

Предполагая, что у вас есть результаты с использованием этой формулы в J3:J10 попробуйте эту формулу, чтобы получить среднее значение в том же формате

=SUMPRODUCT(MID(0&J3:J10,FIND({"D","H","M"},J3:J10)-2,3)/{1,24,1440})/ROWS(J3:J10)

отформатировать ячейку результата как пользовательскую:

d" DAYS, "hh" HRS AND "mm" MIN"

Примечание: это работает при условии, что ни один период не превышает 99 рабочих дней, а средний рабочий день составляет <32 дня