Calculando o tempo entre 5a-1p ou 530a-130p

Question

Eu queria saber se é possível para o Excel calcular o intervalo de tempo se cada bloco de tempo estiver no formato 5a-1p em uma célula. Dessa forma eu poderia adicionar as horas para cada semana sem mudar muito.

Dados da amostra:

Name   Mon     Tues    Wed        Thur   Fri    Sat     Sun     Total Hours 

Bill   2a-10p  5a-1p   730a-330p  OFF    OFF    5p-12a  5a-10a  

Obrigado!

    

Answer 1

A fórmula a seguir dará as horas no período de um dia. Ele funciona analisando o texto e reformatando os horários de início e término em um formato de hora que o Excel reconhece. Feito isso, é uma questão de subtração e conversão do resultado em horas.

=IF(B2="OFF",0,((IF(LEN(RIGHT(B2,LEN(B2)-FIND("-",B2)))>3,LEFT(RIGHT(B2,LEN(B2)-FIND("-",B2)),LEN(RIGHT(B2,LEN(B2)-FIND("-",B2)))-3)&":"&MID(RIGHT(B2,LEN(B2)-FIND("-",B2)),LEN(RIGHT(B2,LEN(B2)-FIND("-",B2)))-2,2),LEFT(RIGHT(B2,LEN(B2)-FIND("-",B2)),LEN(RIGHT(B2,LEN(B2)-FIND("-",B2)))-1))&" "&RIGHT(RIGHT(B2,LEN(B2)-FIND("-",B2)),1)&"m")-(IF(LEN(LEFT(B2,FIND("-",B2)-1))>3,LEFT(LEFT(B2,FIND("-",B2)-1),LEN(LEFT(B2,FIND("-",B2)-1))-3)&":"&MID(LEFT(B2,FIND("-",B2)-1),LEN(LEFT(B2,FIND("-",B2)-1))-2,2),LEFT(LEFT(B2,FIND("-",B2)-1),LEN(LEFT(B2,FIND("-",B2)-1))-1))&" "&RIGHT(LEFT(B2,FIND("-",B2)-1),1)&"m"))*24+IF(AND(MID(B2,FIND("-",B2)-1,1)="p",RIGHT(B2,1)="a"),24,0))

Quebrando:

RIGHT(B2,LEN(B2)-FIND("-",B2)) é a hora final no seu formato.

LEFT(B2,FIND("-",B2)-1) é a hora de início no seu formato.

A substituição desses valores por End e Start ajudará a esclarecer o que a fórmula está fazendo.

=IF(B2="OFF",0,((IF(LEN(End)>3,LEFT(End,LEN(End)-3)&":"&MID(End,LEN(End)-2,2),LEFT(End,LEN(End)-1))&" "&RIGHT(End,1)&"m")-(IF(LEN(Start)>3,LEFT(Start,LEN(Start)-3)&":"&MID(Start,LEN(Start)-2,2),LEFT(Start,LEN(Start)-1))&" "&RIGHT(Start,1)&"m"))*24+IF(AND(MID(B2,FIND("-",B2)-1,1)="p",RIGHT(B2,1)="a"),24,0))

Isso basicamente diz, retorne 0 se B2 for OFF .
Caso contrário, formate a hora para que o Excel a reconheça como um valor de hora.

Para fazer isso, primeiro, verifique quantos dígitos precedem a letra (a ou p) no tempo.

Se houver mais de dois dígitos, o tempo incluirá minutos, portanto, ele precisa ser formatado com dois pontos entre a hora e os minutos. Caso contrário, não faça nada.

Em seguida, coloque um espaço entre os números e a letra e coloque um m após a letra (para torná-lo am ou pm ).

Quando isso for feito para cada um, subtraia o horário de início do horário de término. Isso retorna a fração de um dia no intervalo de tempo, então converta em horas multiplicando por 24.

No caso em que a hora de término é AM e a hora de início é PM, isso resulta em um número negativo. Para corrigir isso, a fórmula verifica essa condição e adiciona 24 ao resultado, se for atendida.

    

Answer 2

Você também pode usar uma versão mais curta:

= SE (B2="DESLIGADO", 0, ABS (MOD (24 * MMULT (GRANDE) (IFERROR (0 + REPLACE) (TRIM (MID (SUBSTITUTO) (SUBSTITUTO (SUBSTITUTO (B2, "a",: 00 AM ")," p ",": 00 PM ")," - ", REPT (" ", 99)), 99 * {0,1} +1,99)), {2; 3} ,": 00 "), 0), {1,2}), {1; -1}), 24) -24 * (IFERROR (MMULT (ENCONTRAR ({" a "," p "}, B2), {- 1 1}) < 0,0))))

Atenciosamente