Fórmula de extração de dados do Excel

Question

Estou tentando executar um relatório para avaliar o tempo de inatividade de dispositivos em uma rede. O relatório do OpManagement não possui nenhuma personalização disponível para relatórios de saída. Por isso, estou procurando uma maneira de extrair os dados e converter os resultados para fornecer uma porcentagem semanal de disponibilidade. Todas as fórmulas falham e só retornam #VALUE! .

Por exemplo, na célula A1: "Linha de Produção do Magazine (Tempo Total de Inatividade: 3d 2h 8m 23s)"

A fórmula deve retornar 55.869% porque 3d = 259200 segundos, 2h = 7200 segundos, 8m = 480 segundos, 23 segundos, que é um total de 266903 segundos acima de 604800 "segundos por semana" ou 44,11% tempo de inatividade. Uma fórmula fará a conversão, mas tenho que extrair manualmente "D, H, M e S primeiro.

=IF(ISNUMBER(FIND("d",A1)),LEFT(A1,FIND("d",A1)-1)*86400,0)
+IF(ISNUMBER(FIND("h",A1)),MID(0&A1,FIND("h",0&A1)-2,2)*3600,0)
+IF(ISNUMBER(FIND("m",A1)),MID(0&A1,FIND("m",0&A1)-2,2)*60,0)
+IF(ISNUMBER(FIND("s",A1)),MID(0&A1,FIND("s",0&A1)-2,2)/604800)

Alguma idéia?

    

Answer 1

Este levou um pouco de trabalho!

O formato da sua pergunta estava um pouco errado, então ACREDITO este é o conteúdo original da sua célula pretendida:

Magazine Production line (Total Downtime :3d 2h 8m 23s)

Assim, com base nisso, essa fórmula extrairá a quantidade de dias e o tempo limite e os colocará no formato de data do Excel.

=DATE(0,1,MID(A$1,FIND(" :",A$1)+2,FIND("d ",A$1,FIND(" :",A$1)+2)-FIND(" :",A$1)-2))+TIME(MID(A$1,FIND("d ",A$1)+2,FIND("h ",A$1,FIND("d ",A$1)+2)-FIND("d ",A$1)-2),MID(A$1,FIND("h ",A$1)+2,FIND("m ",A$1,FIND("h ",A$1)+2)-FIND("h ",A$1)-2),MID(A$1,FIND("m ",A$1)+2,FIND("s)",A$1,FIND("m ",A$1)+2)-FIND("m ",A$1)-2))

O motivo para colocar isso no formato de data do Excel é facilitar a obtenção de uma porcentagem. Como tudo isso é baseado no ano de 1900, fica mais fácil fazer as contas. Se você dividir a fórmula acima em 1/7/1900, receberá sua porcentagem:

=(DATE(0,1,MID(A$1,FIND(" :",A$1)+2,FIND("d ",A$1,FIND(" :",A$1)+2)-FIND(" :",A$1)-2))+TIME(MID(A$1,FIND("d ",A$1)+2,FIND("h ",A$1,FIND("d ",A$1)+2)-FIND("d ",A$1)-2),MID(A$1,FIND("h ",A$1)+2,FIND("m ",A$1,FIND("h ",A$1)+2)-FIND("h ",A$1)-2),MID(A$1,FIND("m ",A$1)+2,FIND("s)",A$1,FIND("m ",A$1)+2)-FIND("m ",A$1)-2)))/DATE(0,1,7)

Isso resulta de um tempo de inatividade 44.13% .

Basta subtrair a coisa toda de 1 e você obterá o resultado final:

=1-((DATE(0,1,MID(A$1,FIND(" :",A$1)+2,FIND("d ",A$1,FIND(" :",A$1)+2)-FIND(" :",A$1)-2))+TIME(MID(A$1,FIND("d ",A$1)+2,FIND("h ",A$1,FIND("d ",A$1)+2)-FIND("d ",A$1)-2),MID(A$1,FIND("h ",A$1)+2,FIND("m ",A$1,FIND("h ",A$1)+2)-FIND("h ",A$1)-2),MID(A$1,FIND("m ",A$1)+2,FIND("s)",A$1,FIND("m ",A$1)+2)-FIND("m ",A$1)-2)))/DATE(0,1,7))

ou 55.87%