Excel. дата и время = рабочая смена X

У меня есть ячейка с датой и временем в формате: (дд-мм-гггг tt: мм)
Исходя из этого, я хотел бы извлечь сдвиг, который сделал данные в моем файле Excel.

Ниже указано время смены

  • Дневная смена: 06: 01-14: 00 (понедельник-четверг), 06: 00-13: 20 (пятница)
  • Вечерняя смена: 14: 01-22: 50 (понедельник-четверг)
  • Ночная смена: 22: 36-06: 00 (вечер воскресенья - утро понедельника) 22: 51-06: 00 (понедельник-пятница)
  • Выходные: 13: 21–01: 20 (пятница) 10: 40–22: 35 (воскресенье)
  • Любое событие вне этой смены должно быть возвращено как смена: Сверхурочная работа

    Я был @ this в течение нескольких часов, но безрезультатно. Кто-нибудь готов принять вызов? :)

if-statement excel datetime vlookup weekday
person Thomas Vester    schedule 20.09.2020    source источник
comment
Покажите, что вы пробовали, и чего вы ожидаете от полученного результата.   -  person Ron Rosenfeld    schedule 20.09.2020
comment
Что ж, мое решение состоит в том, чтобы указать время смены в таблице и ссылку на него, например: = SUM.HVISER (Data! $ BD: $ BD; Data! $ BC: $ BC; ›= & TEKST (B22; дд-мм-åååå) & & TEKST (B21; tt: mm); Data! $ BC: $ BC; ‹= & TEKST (B25; dd-mm-åååå) & & TEKST (B24; tt: mm); Data! $ O : $ O; = & $ G $ 3) Что мне понравилось бы, так это формула, в которой i из ячейки, содержащей дд-мм-гггг tt: мм, имеет ячейку, сообщающую мне сдвиг, который сделал этот ввод данных.   -  person Thomas Vester    schedule 20.09.2020
comment
Вы видите, как сложно читать вашу формулу в том виде, в котором она написана в ваших комментариях? Пожалуйста, отредактируйте свой вопрос, чтобы добавить как формулу, так и пример ввода и желаемого результата.   -  person Ron Rosenfeld    schedule 21.09.2020
comment
Не добавляйте подробности в комментариях. Вместо этого отредактируйте свое сообщение, чтобы включить его там, где его можно будет увидеть. Код, который вы сбросили в комментарии, не читается; если вы поместите его в вопрос, где его можно отформатировать и легко увидеть, его можно использовать.   -  person Ken White    schedule 21.09.2020

Ответы (1)


arrow_upward
0
arrow_downward

Я думаю, что способ сделать это - создать таблицу со столбцами для различных параметров и вычисляемый столбец для дня недели.

Я назвал таблицу Shifts и использую структурированные ссылки для простоты обслуживания и понимания в будущем, но при необходимости вы можете использовать и обычные ссылки.

Таблица смен

введите описание изображения здесь

Формула в E2:

=MATCH([@Day],{"Sunday";"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday"},0)

Формула, которая вернет сдвиг с учетом отметки времени:

=IFERROR(
       LOOKUP(2,1/(
              (MOD(timeStamp,1)>=Shifts[startTime])*
              (MOD(timeStamp,1)<=Shifts[endTime])*
              (WEEKDAY(timeStamp)=Shifts[wdNum])),Shifts[Shift]),
      "Overtime")

случайные результаты

использование формулы в таблице со структурированными ссылками

=IFERROR(
       LOOKUP(2,1/(
              (MOD([@timeStamp],1)>=Shifts[startTime])*
              (MOD([@timeStamp],1)<=Shifts[endTime])*
              (WEEKDAY([@timeStamp])=Shifts[wdNum])),Shifts[Shift]),
      "Overtime")

введите описание изображения здесь

person Ron Rosenfeld    schedule 21.09.2020
comment
Спасибо, что помог Рону. Действительно ценю это! - person Thomas Vester; 26.09.2020