EXCEL - суммировать часы, отображать в произвольном формате, затем выполнять математические операции

Question

Я использую Excel Online, чтобы следить за часами своей работы.

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

Если я просто использую средство форматирования по умолчанию для суммарной ячейки, оно будет отображать "случайное" значение с плавающей запятой, например, 1.20833. Я применяю пользовательский форматер [h]:mm:ss и он, кажется, правильно отображает общее количество часов, которые я провел на работе в ячейке.

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

overtime = number_of_hours_worked - (days_at_work * 7.5)

Но мне не удается выразить это в Excel. Мой вопрос: как я могу извлечь часы (и, надеюсь, минуты) из моей ячейки с пользовательским форматированием?

Смотрите эталонное изображение ниже. Подводя итог, я проработал 29 часов. В течение трех дней я должен работать в общей сложности (7,5 * 3 =) 22,5 часа. Последняя ячейка должна сказать 6.5. Формула в этой ячейке имеет вид =HOUR(N22)-(3*7.5) где N22 - суммированная ячейка.

Answer 1

Проблема не в форматировании, а в формуле.

Использовать этот:

=(N22*24)-(3*7.5)

с ЧАСОМ () вы пропустите любые минуты. Также HOUR возвращает только часть часа, и, поскольку технически это имеет один день и 5 часов, он вернет только 5, а не 29.

Умножая время на 24, вы превращаете время в десятичное число с часами в виде целого числа, а минуты и секунды - в десятичное. Любые дни станут 24 часа.

Answer 2

Ваша текущая формула гласит: возьмите час дня, если время = 29:00, и вычтите 3 * 7,5. В 29:00 действительно 5:00 утра.

У вас уже есть часы, поэтому вы просто хотите вычесть обычную составляющую времени.

=N22-(3*7.5)

должен сделать свое дело.