Separar shifts entre café da manha, almoco e janta

[liviacardinali]

Tenho uma base de dados que tem todos os shifts trabalhados de cada funcionário.

Meu chefe pediu para eu separar a quantidade de horas trabalhadas no café da manha, almoco e jantar. Porem, eu so tenho a entrada e saída de cada funcionario!
Tentei usar chat gpt, tentei lógica e sempre tem algum problema. 

Shifts.xlsx

Adicionei a base de dados aqui.

 

Informacoes:

Cafe da manha: 6h até 11:59
Almoco: 12h até 16:59
Janta: 17h até 5:59 (dia seguinte)

A coluna de café da manha, que o Chat GPT mandou:

Breakfast Hours =

VAR FromTime = 'fShiftReport'[From]

VAR ToTime = 'fShiftReport'[To]

VAR BreakfastStart = TIME(6, 0, 0)

VAR BreakfastEnd = TIME(11, 59, 59)

 

VAR FromTimeInDay = IF(FromTime < BreakfastStart, BreakfastStart, FromTime)

VAR ToTimeInDay = IF(ToTime > BreakfastEnd, BreakfastEnd, ToTime)

 

VAR TotalBreakfastHours =

IF(

    FromTimeInDay > ToTimeInDay && ((HOUR(ToTimeInDay) + MINUTE(ToTimeInDay)/60) - (HOUR(BreakfastStart) + MINUTE(BreakfastStart)/60)) < 0, 0,

    IF( FromTimeInDay > ToTimeInDay && FromTimeInDay > BreakfastEnd, 0,

    IF( FromTimeInDay > ToTimeInDay,

    (HOUR(ToTimeInDay) + MINUTE(ToTimeInDay)/60) - (HOUR(BreakfastStart) + MINUTE(BreakfastStart)/60),

    (HOUR(ToTimeInDay) + MINUTE(ToTimeInDay)/60) - (HOUR(FromTimeInDay) + MINUTE(FromTimeInDay)/60)

)))

RETURN

TotalBreakfastHours

 

A coluna nao esta funcionando em alguns casos, por exemplo

Se o funcionário entra para trabalhar 23h e termina o shift 7h da manha do dia seguinte, a coluna está dando 0 e era para estar resultando 1. 

Por favor alguém poderia me ajudar? Tem alguma forma mais fácil de calcular isso?
Ele quer isso:

 

 

[Joao Raulino]

Suave @liviacardinali?

Você pode usar uma logica mais simplificada, tratando os valores como números, para resolver o teu problema.

A formula da coluna ficaria assim:

Citar

@ Breakfast Hours =

VAR FromTime = HOUR(fShiftReport[From]) + MINUTE(fShiftReport[From])/60

VAR ToTime = HOUR(fShiftReport[To]) + MINUTE(fShiftReport[To])/60

//

VAR BreakfastStart = 6

VAR BreakfastEnd = 12

//

VAR FromTimeInDay =

    SWITCH(

        TRUE(),

        FromTime < BreakfastStart, BreakfastStart,

        FromTime > BreakfastEnd, BreakfastStart,

        FromTime

    )

VAR ToTimeInDay =

    SWITCH(

        TRUE(),

        ToTime > BreakfastEnd, BreakfastEnd,

        ToTime < BreakfastStart, BreakfastStart,

        ToTime

    )

//

VAR TotalBreakfastHours =

    SWITCH(

        TRUE(),

        FromTime < BreakfastStart && ToTime < BreakfastStart, 0,

        FromTime > BreakfastEnd && ToTime > BreakfastEnd, 0,

        ToTimeInDay - FromTimeInDay

    )

RETURN

    TotalBreakfastHours

E o resultado seria esse:

Depois basta você adaptar a mesma ideia para criar as colunas do Almoço e da Janta.

Se essa resposta te ajudou, não se esqueça de marcar como melhor solução 😉

Abs!

[liviacardinali]

O Breakfast eu consegui da seguinte forma:

Breakfast Hours =

VAR FromTime = 'fShiftReport'[From]

VAR ToTime = 'fShiftReport'[To]

VAR BreakfastStart = TIME(6, 0, 0)

VAR BreakfastEnd = TIME(11, 59, 59)

 

VAR FromTimeInDay = IF(FromTime < BreakfastStart, BreakfastStart, FromTime)

VAR ToTimeInDay = IF(ToTime > BreakfastEnd, BreakfastEnd, ToTime)

 

VAR TotalBreakfastHours =

IF(

    FromTimeInDay > ToTimeInDay && ((HOUR(ToTimeInDay) + MINUTE(ToTimeInDay)/60) - (HOUR(BreakfastStart) + MINUTE(BreakfastStart)/60)) < 0, 0,

    IF( FromTimeInDay > ToTimeInDay && FromTimeInDay > BreakfastEnd && (HOUR(FromTime) + MINUTE(FromTime)/60) >= 20 && fShiftReport[Hours] < 4, 0,

    IF( FromTimeInDay > ToTimeInDay && FromTimeInDay > BreakfastEnd && (HOUR(FromTime) + MINUTE(FromTime)/60) >= 20, (HOUR(ToTimeInDay) + MINUTE(ToTimeInDay)/60) - (HOUR(BreakfastStart) + MINUTE(BreakfastStart)/60),

    IF( FromTimeInDay > ToTimeInDay && FromTimeInDay > BreakfastEnd, 0,

    IF( FromTimeInDay > ToTimeInDay,

    (HOUR(ToTimeInDay) + MINUTE(ToTimeInDay)/60) - (HOUR(BreakfastStart) + MINUTE(BreakfastStart)/60),

    (HOUR(ToTimeInDay) + MINUTE(ToTimeInDay)/60) - (HOUR(FromTimeInDay) + MINUTE(FromTimeInDay)/60)

)))))

 

RETURN

TotalBreakfastHours

 

O problema esta no Dinner agora 😞 Estou tentando e nao vai de jeito nenhum

 

 

Dinner Hours =

VAR FromTime = 'fShiftReport'[From]

VAR ToTime = 'fShiftReport'[To]

VAR DinnerStart = TIME(17, 0, 0)

VAR DinnerEnd = TIME(5, 59, 59)

VAR LunchEnd = TIME(16,59,59)

VAR BreakfastStart = TIME(6,0,0)

 

VAR FromTimeInDay =

IF(FromTime = 0, 0, IF(FromTime > DinnerEnd && FromTime < DinnerStart, DinnerStart, FromTime))

 

VAR ToTimeInDay = IF(ToTime > DinnerEnd && ToTime < FromTime, DinnerEnd + 1,

 IF( ToTime > DinnerEnd && ToTime > FromTime, DinnerEnd + 1,

    ToTime))

//IF(ToTime > DinnerEnd && ToTime < FromTime, DinnerEnd + 1, ToTime)

 

VAR TotalDinnerHours =

    IF((HOUR(FromTime) + MINUTE(FromTime)/60) >= 6 && (HOUR(ToTime) + MINUTE(ToTime)/60) <= 16.99 && (HOUR(FromTimeInDay) + MINUTE(FromTimeInDay)/60) >= 17 && (HOUR(ToTimeInDay) + MINUTE(ToTimeInDay)/60) <= 5.99,

    ((HOUR(FromTimeInDay) + MINUTE(FromTimeInDay)/60) - 0 + 23.99 - (HOUR(ToTimeInDay) + MINUTE(ToTimeInDay)/60)),

 

    IF((HOUR(FromTime) + MINUTE(FromTime)/60) >= 6 && (HOUR(ToTime) + MINUTE(ToTime)/60) <= 16.99, 0,

    IF((HOUR(ToTimeInDay) + MINUTE(ToTimeInDay)/60) > (HOUR(FromTimeInDay) + MINUTE(FromTimeInDay)/60) && (HOUR(FromTime) + MINUTE(FromTime)/60) > 6 && ((HOUR(ToTime) + MINUTE(ToTime)/60)) < 16.99, 0,

    IF(

        (HOUR(ToTimeInDay) + MINUTE(ToTimeInDay)/60) < (HOUR(FromTimeInDay) + MINUTE(FromTimeInDay)/60),

        ((HOUR(FromTimeInDay)-12) + MINUTE(FromTimeInDay)/60) - (HOUR(ToTimeInDay) + MINUTE(ToTimeInDay)/60),

    (HOUR(ToTimeInDay) + MINUTE(ToTimeInDay)/60) - (HOUR(FromTimeInDay) + MINUTE(FromTimeInDay)/60)

)))

)

 

RETURN

(HOUR(ToTimeInDay) + MINUTE(ToTimeInDay)/60)

 

Na formula acima, acredito que o FromTimeInDay e o ToTimeInDay estejam errados. Exemplo:

From 06:00 until 14:00

o FromTimeInDay esta dando 17h e o ToTimeInDay 5:59... e é pra ser das proprias 6 as 14.
Dai o resultado final esta dando 35 horas e era pra ser 0

Estou enlouquecendo com essa fórmula