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

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

  • 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)
|источник

1 ответ1

0

Спасибо за помощь, но это то, что я закончил сам (предположим, что начальная строка 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),"")
|источник

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .