Chcete vypočítať koľko hodín trvala pracovná cesta od 20.2.2006 9:00 do 22.2.2006 12:30 a tiež spočítať celkový čas niekoľkých ciest. Budete potrebovať dátumové, časové, textové, logické a štatistické funkcie Excelu , ako aj bežné vzorce. Budete chcieť, aby výsledok vyzeral približne ako na obr. Bude potrebné urobiť aj nejaké pomocné výpočty (bunky F3:G8) aby ste mohli spočítať celkový čas všetkých ciest.
V prvom rade treba napísať, že výpočet =C3+D3-A3-B3 funguje iba ak je cesta v priebehu jedného dňa. Pri viacdňovej je výsledok skreslený, lebo časový formát bunky ráta len s 24 hodinovým cyklom. Preto je potrebné urobiť vzorec, ktorý bude zo vstupných hodnôt oddeľovať deň a čas. Ak bude čas cesty viac ako jeden deň, tak musíte počet dní vynásobiť číslom 24 a k nemu pripočítať zvyšné hodiny, aby mohol byť výsledok napr. 27 hodín. Výsledkom výpočtu bude textový formát bunky (potrebujete spojiť hodiny, dvojbodku a minúty) s časom cesty. Vzorec by mohol vyzerať takto (bunka E3):
=CONCATENATE(DEN(C3+D3-A3-B3)*24+HODINA(C3+D3-A3-B3);":";MINUTA(C3+D3-A3-B3))
Problém tohto výsledku je, že nedokážete spočítať viac časov, lebo formát bunky je textový. Musíte urobiť pomocné výpočty na zmenu formátu z textového na číselný a ktoré znovu oddelia hodiny a minúty. Oddelenie môžete urobiť textovými funkciami a ako oddeľovací znak použite dvojbodku. Vzorce môžu vyzerať takto (F3, G3):
=HODNOTA(ZLEVA(E3;HLEDAT(":";E3;1)-1))
=HODNOTA(ZPRAVA(E3;DÉLKA(E3)-HLEDAT(":";E3;1)))
Výsledok vzorcov budú dve číselné hodnoty (F7 a G7), ktoré môžete spočítať funkciou =SUMA(F3:F6) a =SUMA(G3:G6). Ďalší problém je súčet minút (G7), pretože môže byť viac ako 60 a preto potrebujete ešte pomocné výpočty, ktoré pripočítajú každých 60 minút ako celú hodinu, k celkovému súčtu hodín a zvyšok ponechajú ako minúty. Vzorce môžu vyzerať takto (F8, G8):
=KDYŽ(G7=0;0;KDYŽ(G7=60;1;HODNOTA(ZLEVA(G7/60;HLEDAT(",";G7/60;1)-1))))
=KDYŽ(G7=0;0;KDYŽ(G7=60;0;(G7/60-HODNOTA(ZLEVA(G7/60;HLEDAT(",";G7/60;1)-1)))*60))
Výsledkom týchto vzorcov sú číselné hodnoty, ktoré stačí pripočítať k už vypočítanému súčtu celých hodín. Konečný výsledok súčtu hodín bude v bunke F9 (=F7+F8) a súčet minút bude v G9 (=G8).
Pre anglický Excel sú funkcie nasledovné:
DEN = DAY
HODINA = HOUR
MINUTA = MINUTE
HODNOTA = VALUE
ZLEVA = LEFT
HLEDAT = SEARCH
ZPRAVA = RIGHT
DÉLKA = LEN
KDYŽ = IF
Netvrdím, že toto je jediné riešenie problému, ale napadlo ma ako prvé a nad iným som už neuvažoval. Ak niekto tieto vzorce začne používať bol by som rád, keby ste sa o tom zmienili v diskusii, aby som videl či tento článok niekomu poslúžil :-).
