Формула "= SUMIF" не работает с форматированием 00/:00

Question

Доброе утро,

У меня есть таблица, над которой я работаю, и мне необходимо автоматически рассчитать общее время, проведенное в месте, в зависимости от времени начала и окончания. Это работает, если я сохраню форматирование для всех столбцов как «чч: мм», однако я хотел изменить форматирование на «00/: 00», чтобы мне не пришлось беспокоиться об использовании ключа двоеточия, и я могу просто ввести цифры (электронная таблица должна обновляться быстро и желательно с использованием только одной руки).

Когда я меняю форматирование, автоматическая сумма перестает работать. Любые идеи, или есть ли другой способ автоматического ввода двоеточия без необходимости вручную вводить его? Я также приложил фотографию того, что я имею в виду.

Спасибо!

Answer 1

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

Таким образом, чтобы получить правильную сумму, нам нужно разобрать число в часы и минуты и разделить на соответствующие части:

часы работы:

INT(Range/100)/24

минут

MOD(range,100)/(24*60)

Используя SUMPRODUCT, мы можем перебрать диапазон, добавив результаты:

=SUMPRODUCT((D1:D8="x")*((INT(C1:C8/100)/24+MOD(C1:C8,100)/(60*24))-(INT(B1:B8/100)/24+MOD(B1:B8,100)/(60*24)))

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