Формула Excel для расчета суммы сверхурочных

Question

Мне нужна помощь в подсчете количества часов старше 40. Это таблица заработной платы, и я хочу рассчитать сверхурочные.

Если Q4 больше 40 часов, то вычтите 40 часов из Q4 и отобразите разницу в количестве часов. Если ячейка меньше 40 часов, вернуть 0.

Вот что у меня есть:

=IF(Q4>40,Q4-40,0)

Но при расчете на ячейку, содержащую 49:23, она всегда возвращает 00:00

Исходная ячейка имеет пользовательский формат [ЧЧ]: ММ, поскольку при вычислении ячеек для получения общего количества часов не будет отображаться часов свыше 12.

Answer 1

Кажется, что значение в ячейке Q4 является DateTime. В 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 .

Answer 2

Excel хранит время (и даты) как дни. Выбранный формат отображения не влияет на это (и не имеет отношения к вашей проблеме).

Вы должны делать свою математику в днях, а не в том, что вы видите. Если вы хотите проверить «> 40 часов», выберите «> 40/24 дней».

Это было бы =IF(Q4>40/24,Q4-40/24,0) для вашего примера.