Формула Excel: Узнайте время, проведенное в определенных временных диапазонах

368
SupaMonkey

Попытка составить немного более сложную сверхурочную работу здесь:

Работнику платят:

  • 1 раз в час в будние дни (пн-пт) с 08:30 до 17:30
  • 1,5-часовая ставка в будние дни (пн-пт) до 08:30 или после 17:30
  • 1,5-часовая ставка по субботам
  • 2х почасовая ставка по воскресеньям и в праздничные дни

Макет листа:

|Date |Day |Slip No |Name |Destination |Start Time |End Time |Total Hours Worked |Basic Hours |OT @ 1.5 |OT @ 2.0

Поэтому я ищу формулы для столбцов

  • [H] = Отработанные часы
  • [I] = Основные часы
  • [J] = ОТ @ 1,5
  • [K] = ОТ @ 2.0

Я только ищу чч: мм представление того, что работало; поэтому нет необходимости в поле «почасовая ставка».

[H] =MOD(G6-F6,1) [I] =IF(F6<G6,MIN(G6,Data!F2)-MAX(F6,Data!E2),MAX(0,Data!F2-F6)+MAX(0,G6-Data!E2)) [J] =H6-I6 [K] =IF(OR(WEEKDAY($A6)=1,ISERROR(VLOOKUP($A6,tblPublicHolidays,1,FALSE))=FALSE),$H6,0)

[H] = работает;

[K] = работает;

[I] / [J] - это проблема, и я чувствую, что если бы я мог просто начать работать, у меня тоже будет решение для J. J - это просто «общее количество часов» минус «базовые часы».

tblPublicHolidays - это список выходных дней, которые у нас есть.

Данные! E2 = время начала - то есть: 08:30

Данные! F2 = время окончания - то есть: 17:30

Для этой демонстрации первая строка (6) имеет значение времени начала 18:30 и время окончания 18:45. Я получил формулу [I] с https://exceljet.net/formula/total-hours-that-fall-between-two-times, но, похоже, она не работает по следующим причинам:

  • [I] отображается как набор хешей (#), если у меня есть формат «Время»
  • Если я изменю [I] на числовой формат, то он будет отображаться как -0.04
  • Кажется, у [J] всегда на 1 час больше, чем предполагалось (в этом примере 1:15 вместо 0:15)
0
Вы можете получить #s, если содержимое слишком широко для отображения в ширине столбца. Что вы видите, если вы настраиваете ширину столбца? fixer1234 5 лет назад 0
Извините, я не упомянул об этом; Я сделал изменение размера столбца - ничего не делает. Только изменение формата на «число» дает -0,04, о котором я упоминал. SupaMonkey 5 лет назад 0
Возможно ли получить отрицательное время в [I] (возможно, связанное с тем, что дает вам ошибку в 1 час в [J])? -.04 эквивалентно примерно 1 часу (т. Е. 0,04 дня). Если вы не хотите давать результат с отрицательным временем, это может быть подсказкой. Если допустимое отрицательное время допустимо, причина, по которой вы получаете #s, вероятно, связана с тем, что вы работаете в Windows и используете «1900-й базис» для даты / времени, который не способен обрабатывать отрицательные времена. Версия Excel для Mac по умолчанию - «1904», которая может обрабатывать отрицательные времена, и вы можете выбрать это в версии Windows. fixer1234 5 лет назад 0
Итак: `Данные! E2 = 8: 30` &` Данные! F2 = 17: 30` [ссылка] (https://uploads.disquscdn.com/images/e7643136925984892b7e6ae61bdb03826a2a52ef97f8e340d082fc74e86e740c.jpg, 08:18 G = 0840 G0) 08 (G) : 30 в первом случае `` MAX (F6, lower) = 18:30 в первом случае` Последние два примера, где «время начала» / «время сбора» предшествует «нижнему» - работает нормально Первый два примера, где «время начала» / «время сбора» находится после «верхнего» - это не работает. SupaMonkey 5 лет назад 0
Звучит очень похоже на [это] (https://stackoverflow.com/questions/51721083/calculate-the-weighted-minutes-between-two-dates-in-excel/51763302) .. Пожалуйста, рассмотрите возможность добавления образцов данных (у вас есть вставил ваши формулы, что хорошо .. но у других нет возможности проверить это ..) p._phidot_ 5 лет назад 0

1 ответ на вопрос

0
SupaMonkey

Спасибо за помощь, но это то, что я закончил сам (предположим, что начальная строка 6):

A6 (DATE) B6 (DAY) =IF(A6<>"",TEXT(A6,"dddd"),"") F6 (START TIME) I6 (END TIME) L6 (HOURS WORKED) =IF(AND(F6>0,I6>0),MOD(I6-F6,1)*24,"") M6 (BASIC HOURS) =IF(AND(F6>0,I6>0),IF(AND(WEEKDAY($A6)>1,WEEKDAY($A6)<7),IF(F6 < I6,MAX(0,MIN(I6,upper)-MAX(F6,lower))*24,MAX(0,upper - F6)+MAX(0,I6 - lower)*24),0),"") N6 (Overtime @ 1.5) =IF(AND($F6>0,$I6>0),IF(AND(WEEKDAY($A6)>1,ISERROR(VLOOKUP($A6,tblPublicHolidays,1,FALSE))=TRUE),(L6-M6),0),"") O6 (Overtime @ 2.0) =IF(AND($F6>0,$I6>0),IF(OR(WEEKDAY($A6)=1,ISERROR(VLOOKUP($A6,tblPublicHolidays,1,FALSE))=FALSE),$L6,0),"")

Похожие вопросы