W Excelu panuje święta zasada: jedna komórka = jedna informacja. W VBA często przetwarzamy dane pochodzące z różnych systemów IT. Czasami nie są one oddzielone żadnym separatorem – dla konkretnej informacji przeznacza się określoną liczbę znaków. W jaki sposób wyciągnąć z takiego raportu np. czas? O tym przeczytasz w tym tekście.
Dziwny format czasu
Oryginalne dane znajdują się w kolumnie A.
Na czerwono wyróżniłem czas, który chcemy wyciągnąć do osobnej komórki. Mierzy on opóźnienie w dostawie pomiędzy magazynem a produkcją.
Czas jest zaszyty gdzieś pomiędzy znakami 39 i 48 włącznie. Na screenie widać jednak, że informacja zaczyna się w różnych miejscach. Mam tu na myśli godziny, bowiem dwukropek znajduje się zawsze w znaku nr 46, a na pozycjach 47 i 48 mamy pokazane minuty.
Screen z rozwiązaniem
Formuła wyciągająca czas znajduje się w kolumnie B (format ogólny) i w kolumnie C (format wyświetlający łączną liczbę godzin i minut).
Założenia do formuły
Aby wyciągnąć informację dotyczącą czasu, musiałem uwzględnić i przewidzieć kilka rzeczy:
- Znaki dotyczące godziny (39-45) mogą zawierać znak kropki. Jest to sytuacja gdy tych godzin jest 1000 lub więcej.
- Znaki dotyczące godziny (39-45) mogą 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 pokazują minuty. Trzeci znak od prawej (dwukropek) rozdziela godziny od minut.
- Jedna doba to 24 godziny czyli 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 taką formułę, która okazała się skuteczna.
=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))
Wygląda na skomplikowaną, więc rozłóżmy ją na czynniki pierwsze.
Wyciąganie godzin
Bazujemy na znakach od 39 do 45.
- Najpierw 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 np. „10659” muszę sprawdzić ile to 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!
- Zabezpieczam się na wypadek wystąpienia błędu funkcją JEŻELI.BŁĄD. Gdy przed dwukropkiem będę miał same puste pola – pojawi się błąd. W takiej sytuacji wiadomo, że liczba pełnych godzin to 0.
Wyciąganie minut
- Funkcją FRAGMENT.TEKSTU wyciągam dwie cyfry znajdujące się po dwukropku. Nie mogę użyć funkcji PRAWY ponieważ naszym źródłem jest cały wpis z komórki A2.
- Mając wyciągnięty wpis np. „37” muszę przekonwertować go do postaci czasu. Chodzi o ustalenie jaką część doby stanowi 37 minut – wychodzi 0,0256944444444444
Sumowanie godzin i minut
Dodaję do siebie obie wartości i otrzymujemy wynik 444,150694444444 (444,125 + 0,0256944444444444).
Wychodzi nam więc 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.
