Как посчитать сверхурочные часы в excel

Обновлено: 07.07.2024

Моя девушка работает из дома 6 часов в день и зарабатывает себе на жизнь. Она попросила меня составить для нее табель рабочего времени, в котором она могла бы ввести свое время начала и окончания и автоматически рассчитать ее часы. Он вычисляет общее количество отработанных часов без проблем (если они не проходят полночь), но я также хотел, чтобы оно отслеживало сверхурочные часы для нее.

У меня есть формула, которая должна работать, но проблема в том, что, если все время начала и окончания в данный день являются как AM, так и PM, то он не будет рассчитывать сверхурочные часы, независимо от того, сколько общих часов превысит 6 часов. Однако, как только вы введете значение времени, которое начинается в AM, и заканчивается в PM, например, с 11:00 до 14:00, оно вычисляет общее количество часов сверхурочной работы для дня.

Вот снимок экрана табеля рабочего времени с указанием времени, в котором общее количество часов превышает 6, но нет времени начала и окончания, переходящего из AM в PM:

Вот пример, когда последний раз, введенный в первый день, теперь идет от времени AM к времени PM. Обратите внимание, что сверхурочные часы теперь точно рассчитываются.

VBA, генерирующий формулы для расчета часов, выглядит следующим образом:

2 ответа

В настоящее время ваша формула для суммирования часов на неделе, по сути:

Проблема в том, что когда вы выходите за полночь, у вас нет возможности указать, что время на следующий день. Поскольку время хранится как десятичное число, а дата хранится как целое число, и по умолчанию, когда вводится только время, 0 является целым числом для дня. Все, что вам нужно сделать, это добавить 1 к времени, когда время ожидания меньше, чем время. Собираюсь немного обмануть и предположить, что между входом и выходом никогда не будет больше 24 часов. Таким образом, имея в виду этот обман и логические значения True и False, преобразованные в 1 и 0 соответственно при выполнении математического оператора, вы можете настроить свою формулу следующим образом:

В качестве альтернативы вы можете записать часы в тот день, когда они работали, а не по времени, когда вы спали в течение ночи в качестве разграничителя дня. То есть, если вы работаете с 23:00 в понедельник до 01:00 во вторник, то записывайте понедельник как время в 23:00 и время ожидания как 23:59, а вторник записывайте время как 00:00 и время ожидания как 01:01. Обратите внимание на добавленную 1 минуту к тайм-ауту. Это должно восполнить пропущенную минуту между 23:59 и 24:00 (которая технически не существует в Excel, но работает с некоторыми операциями)

Что касается расчета сверхурочных, см. Комментарий Винсента Г. Еще один вариант этой формулы:

Вам нужно будет изменить ссылки на ячейки в соответствии с вашими потребностями в программировании на VBA. Обе формулы корректируют расчет сверхурочных и времени начала и окончания, пересекающего полночь.

Во-первых, если вы пойдете за полночь, вот что я бы сделал:

Переход за полночь означает, что часы будут отрицательными. В этом случае выполните оператор IF, в котором, если результат является отрицательным числом, просто добавьте к результату 24 часа (или 1 день).

Во-вторых, не могли бы вы рассчитать сверхурочную работу без VBA, просто вычтя 6 часов из общего рабочего времени за день? Я не слишком знаком с VBA, поэтому, если я упускаю что-то явно очевидное, пожалуйста, прости меня.

Подсчет зарплаты по сменам в кафе
есть такая задача. ссылкасразу на документ google с прави правки документа, очень прошу не.

Учет сверхурочных часов по неделям и за год
Здравствуйте! Передо мной была поставлена задача ведения учета сверхурочных часов. Есть.

Подсчет зарплаты
Всем доброго дня, ребят я новичек в sql да и вообще в базах в целом, наведите на мысль как можно.

Подсчёт сдельной зарплаты
Небольшое производство. Несколько операций. Нужно подсчитать, кто и что сколько сделал. Ситуация.

Ведь в зависимости от графика, норма выработки у каждого человека разная - 40 или 48 часов. Судя по таблице по графику 12ч в режиме 2/2 работают 3 и 7 сотрудники. Остальные явно по 8ч в режиме 5/2.
Соответственно, добавляем в таблицу столбцы:
1. J: "Итого часов", где считаем общую наработку в часах у каждого.
2. K: "Норматив (часов)", где у всех, кроме 3 и 7 пишем =8*5, а у 3 и 7 = 12*4
3. L: "Переработка", где вычитаем "Норматив" из "Итого часов".
4. M: "Нормочас", где у всех проставляем 1200.
5. N: "Зарплата", с формулой =M2*(K2+2*L2), растянутой вниз. - это ответ на вопрос 1.
6. Под последним столбцом считаем итоговую сумму - это ответ на вопрос 2. Такой вариант я рассматривал. Просто если таких людей будет не 10, а 100, будет труднее. Нет возможности в одной формуле сделать и подсчет, и проверку на норму в зависимости от графика?

Решение

Нет возможности в одной формуле сделать и подсчет, и проверку на норму в зависимости от графика?

Есть. Просто в исходном посте нет ни намёка ни на расширяемость задачи, ни на подобную необходимость.
Но для этого надо сначала предусмотреть возможное появление следующих проблем:
1. "Норматив" должен браться не на основании очевидности, а задаваться на основании данных трудового договора. Поскольку навскидку на ум приходит как минимум пяток резонов, вследствие которых очевидное становится неверным, а если покопаться в практике - их наверняка поболее будет.
2. Стоимость нормочаса с вероятностью около 80% должна быть задана отдельно для каждого (обычно зарплаты у людей разные, часто даже на одинаковой должности, и нормочас может для каждого рассчитываться из той суммы, которая прописана в качестве месячного оклада в его трудовом договоре. Вы уверены, что в вашем случае это не так, и нормочас для всех абсолютно одинаков? В ведомости не попадутся ни руководители среднего звена, ни их замы, ни другие должности, чей нормочас отличается от среднего? Даже сотрудники на одинаковой должности, принятые на работу с разницей в 2-3 года могут иметь различия в договорных пунктах. Если нет твёрдой уверенности в этом, то и без колонки "стоимость нормочаса" не обойтись.
3. Если для сотрудников 5/2 расчетная неделя = 40ч/7 дней, то для 12ч 2/2 расчетная неделя= 48ч/8 дней, которая может условно приводиться к рабочей неделе 40ч/7 дней. а может считаться на основании месяца. Не произойдёт очередная смены исходной парадигмы после очередного написанного решения на основании недельной таблицы? И как здесь рассчитывать нормативные часы для 12х2/2: по 48ч/неделя? по 40ч/неделя? Или другие алгоритмы?

Как понимаете, одной формулой всё это можно сделать, только если это чисто теоретическая задачка, в которой на все эти пункты преткновения ответ один: А пофиг! Это ваш случай?

Как правило, при расчете рабочего времени необходимо исключить перерыв. Здесь, в этом руководстве, представлена формула, которая использует функцию MOD для расчета сетевого времени с перерывами в Excel.

Если вы хотите следовать этому руководству, загрузите пример электронной таблицы.

Общая формула:

MOD(work_end-work_start,1)-MOD(break_end-break_start,1)

аргументы

Work_start: the time that starts work at.

Work_end: the time that ends work at.

Break_start: the time that starts break at.

Break_end: the time that ends break at.

Как работает эта формула

Вот таблица, содержащая рабочее время и время перерыва. Используйте приведенную ниже формулу для расчета сетевого времени.

Нажмите Enter ключ для получения сетевого времени.

Затем перетащите дескриптор автозаполнения по ячейкам, чтобы рассчитать сетевое время.

объяснение

Функция MOD возвращает остаток от деления двух чисел. Например, МОД (8,3) = 2 .

В этом случае значение времени должно быть десятичным значением, которое останется самим собой при делении 1 с помощью функции MOD.

Например, = MOD (C6-B6,1) = 10: 00 = 0.416666667, затем = MOD (E6-D6,1) = 1: 15 = 0.052083333. В настоящее время
= MOD (C6-B6,1) -MOD (E6-D6,1)
= 10: 00–1: 15
= 8: 45

замечание

1) Если вам нужно рассчитать общее время в сети, используйте формулу

Внимание: Результат суммы автоматически отображается в формате ч: мм. Чтобы легко понять результат суммы, вы можете изменить его на формат времени 37:30:55.

2) Если в расписании записано два отдельных времени входа / выхода для одной смены, как показано ниже, вы можете использовать следующую формулу:

MOD (конец работы1-начало работы1,1) -MOD (конец работы2-начало работы2,1)
= MOD (C17-B17,1) + MOD (E17-D17,1)

Относительные формулы

Относительные функции

Лучшие инструменты для работы в офисе

Kutools for Excel - поможет вам выделиться из толпы

Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и так далее . ) и экономия 80% времени для вас.

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

Рассчитать сверхурочные и оплату

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

1. Примените формулу, чтобы сначала отобразить обычный рабочий час. В ячейке F1 введите эту формулу = ЕСЛИ ((((C2-B2) + (E2-D2)) * 24)> 8,8, ((C2-B2) + (E2-D2)) * 24) и перетащите маркер автозаполнения вниз, чтобы отобразить обычный рабочий час каждого дня. См. Снимок экрана:

2. В ячейке G2, в которой будет отображаться сверхурочная работа, введите эту формулу = ЕСЛИ (((C2-B2) + (E2-D2)) * 24> 8, ((C2-B2) + (E2-D2)) * 24- 8,0), перетащите дескриптор Автозаполнение вниз, чтобы применить эту формулу к ячейкам, теперь сверхурочная работа каждого дня рассчитана. См. Снимок экрана:

Вы можете рассчитать общее количество рабочее время и платежи.

3. В ячейке F8 введите эту формулу = СУММ (F2: F7) и перетащите вправо, чтобы заполнить эту формулу до G8, теперь общее количество обычных и сверхурочных часов получено.

4. В ячейках, в которых вы хотите рассчитать оплату за обычные и сверхурочные часы, введите отдельно формулу = F8 * I2 и формулу = G8 * J2, см. Скриншоты:

Вы можете применить = F9 + G9 для расчета общей суммы платежей, которые включают регулярные платежи и сверхурочные выплаты.

Читайте также: