Niedawno pracowałem nad aplikacją, która w pierwszym kroku miała za zadanie zaimportować dane z pliku tekstowego i wyciągnąć formułą m.in. dziwnie sformatowany czas.
Screen
Oryginalne dane znajdują się w kolumnie A. Formuła wynikowa znajduje się w kolumnie B (format ogólny) i w kolumnie C (format wyświetlający łączną liczbę godzin i minut).
Dziwny Format Czasu
Moją uwagę przykuł dość dziwnie zapisany czas (sformatowany na czerwono) mierzący opóźnienie w dostawie. Dane te były zaszyte między znakami 39 i 48 włącznie, przy czym jak widać na screenie, zaczynały się one w dowolnym miejscu. Mam tu na myśli godziny, bowiem dwukropek znajdował się zawsze na pozycji nr 46, a na pozycjach 47 i 48 mieliśmy pokazane minuty.
Założenia Do Formuły
Aby wyciągnąć tą informację dotyczącą czasu musiałem uwzględnić i przewidzieć kilka warunków.
- zakres między znakiem 39 a 45 (godzina) może zawierać znak kropki – jeśli tych godzin jest aż 1000 lub więcej
- zakres między znakiem 39 a 45 (godzina) może zawierać puste pola
- w przypadku gdy łączny czas jest mniejszy od godziny – godziny nie są wyświetlane, tylko dwukropek i minuty
- dwa pierwsze znaki z prawej strony pokazywały minuty, trzeci znak od prawej (dwukropek) rozdzielał godziny od minut
- jedna doba to 24 godziny lub 1440 minut.
Analiza Formuły
Zdecydowałem się, że za pomocą funkcji SUMA połączę godziny z minutami tzn. będę osobno wyciągał jedno i drugie. Wykombinowałem takie coś:
=SUMA(JEŻELI.BŁĄD(USUŃ.ZBĘDNE.ODSTĘPY(PODSTAW(FRAGMENT.TEKSTU($A2;39;7);”.”;””)/24);0); JEŻELI.BŁĄD((FRAGMENT.TEKSTU($A2;47;2)/1440);0))
Formuła wygląda na dość skomplikowaną. Przyjrzyjmy się jej:
Wyciąganie Godzin
Bazujemy na znakach od 39 do 45.
- w pierwszej kolejności zamieniam ewentualne kropki na puste pola (kasuję je). W pierwszym przypadku zamiast ” 10.659″ mam więc ” 10659″.
- następnie kasuję zbędne spacje. Zamiast ” 10659″ mam teraz “10659”.
- mając podaną liczbę godzin “10659” muszę sprawdzić ile to faktycznie jest dni. Skoro cyfra 1 to to samo co 24 godziny to muszę podzielić “10659” przez 24. W wyniku otrzymuję 444,125. Czyli 444 dni i 3 godziny. Niezłe opóźnienie!
- przezornie zabezpieczam się na wypadek wystąpienia błędu funkcją JEŻELI.BŁĄD – gdy przed dwukropkiem będę miał same puste pola błąd się pojawi. W takiej sytuacji wiadomo, że liczba pełnych godzin to 0.
Wyciąganie Minut
- funkcją FRAGMENT.TEKSTU wyciągamy dwie cyfry znajdujące się po dwukropku. Nie możemy użyć funkcji PRAWY ponieważ naszym źródłem jest cały wpis z komórki A2.
- mając wyciągnięty wpis “37” musimy przekonwertować to do postaci czasu tzn. ustalić jaką część doby stanowi 37 minut – wychodzi 0,0256944444444444
Sumowanie Godzin i Minut
Dodajemy do siebie obie wartości i otrzymujemy wynik 444,150694444444 (444,125 + 0,0256944444444444).
Czyli wychodzi nam jakaś bardzo dziwna liczba (jest to opóźnienie liczone w dniach). Jeśli chcemy wyświetlić to opóźnienie w formie łącznej liczby godzin, musimy nadać danym format [g]:mm, co pokazuje kolumna C.
