Вопрос или проблема
Мне нужна помощь в расчете количества часов сверх 40. Это таблица для расчета заработной платы, и я хочу рассчитать сверхурочные.
Если Q4 больше 40 часов, то вычтите 40 часов из Q4 и отобразите разницу в количестве часов. Если ячейка меньше 40 часов, верните 0.
Вот что у меня есть:
=IF(Q4>40,Q4-40,0)
Но когда я вычисляю для ячейки, содержащей 49:23, всегда возвращает 00:00.
Исходная ячейка имеет специальный формат [HH]:MM, потому что она не показывала часы свыше 12 при расчете для получения общего количества часов.
Excel хранит время (и даты) как дни. Выбранный формат отображения не влияет на это (и не связан с вашей проблемой).
Вам нужно выполнять расчеты в днях, а не в том, что отображается. Если вы хотите проверить на “> 40 часов”, проверяйте на “> 40/24 дня”.
Это будет =IF(Q4>40/24,Q4-40/24,0) для вашего примера.
Кажется, значение в ячейке Q4 – это дата и время. В Excel это хранится как число, где целая часть – это количество дней, а дробная часть – это время суток. Чтобы получить количество часов вместо количества дней, нужно умножить на 24. Следующая формула даст вам количество часов сверх 40.
=IF(Q4*24>40,Q4*24-40,0)
Если Q4 содержит 49:23, эта формула возвращает 9.3833. Убедитесь, что ячейка, содержащая часы сверхурочных, отформатирована как число (или “Общее”), а не как время.
Если вы хотите игнорировать дробные часы, используйте функцию INT:
=IF(Q4*24>40,INT(Q4*24-40),0)
Как отмечает Скотт в своем комментарии (спасибо, Скотт), повторение Q4*24-40 в формуле неэстетично и может привести к ошибкам, если формула нужно будет изменить. Лучше версия первой формулы будет такой:
=MAX(Q4*24-40,0)
А лучшая версия второй будет:
=MAX(INT(Q4*24-40),0)
Эти формулы работают, потому что всякий раз, когда Q4*24 меньше 40, Q4*24-10 будет меньше нуля, и функция MAX вернет 0 вместо Q4*24-40.
Ответ или решение
Ваша задача заключается в расчете сверхурочных часов для расчета зарплаты, и, как вы упомянули, вам необходимо правильно обрабатывать формат времени в Excel, чтобы правильно вычислить количество часов, превышающих 40.
Проблема, с которой вы столкнулись, заключается в том, что Excel хранит время как дробные числа, где целая часть представляет дни, а дробная часть – время. Чтобы сделать правильные вычисления с часами, вы должны использовать единицы измерения времени, представленные в часах, а не в днях.
Решение:
Вот формула, которую вы можете использовать для расчета количества сверхурочных часов:
=MAX(Q4*24-40, 0)В этом решении:
- *Q424** преобразует значение в часах: поскольку 1 день равен 24 часам, умножив на 24, вы получаете общее количество часов.
- -40 вычитает 40 стандартных рабочих часов.
- MAX(…, 0) гарантирует, что если результат отрицательный (что означает, что часы меньше 40), будет возвращено 0, а не отрицательное число.
Если нужно округлить до целых часов:
Если вы хотите игнорировать дробные часы и рассчитать только полные часы, вы можете использовать формулу с функцией INT:
=MAX(INT(Q4*24-40), 0)Эта формула работает аналогично предыдущей, но она округляет результат вниз до ближайшего целого числа, игнорируя любые дробные часы.
Форматирование ячейки:
Не забудьте, что ячейка, где вы отображаете результат, должна быть отформатирована как "Число" или "Общее", а не как "Время", чтобы отображать часы корректно.
Пример:
Допустим, ячейка Q4 содержит значение времени 49:23 (что эквивалентно 49.3833 часов). Подставив это значение в формулу, вы получите следующий результат:
- Используя первую формулу:
49.3833*24 - 40= 9.3833 (что эквивалентно 9 часам и 23 минутам). - Используя формулу с
INT:INT(49.3833*24 - 40)= 9 (только полные часы).
Таким образом, указанные формулы позволят вам корректно отобразить количество сверхурочных часов в зависимости от значения в ячейке Q4. Если у вас будут дополнительные вопросы по данному вопросу, не стесняйтесь обращаться.