„Safra, jak mám převést Ticks na čas Excelu?“ ozval se mi kolega přes Skype. Popravdě jsem v danou chvíli neměl tušení a tak se dobral řešení dříve než já. Pokud vám tento problém způsobuje „tiky“ v oku, zkuste si přečíst článek.
Ticks (v .NET Framework 4 jde o vlastnost DateTime.Ticks) je údaj ve stovkách nanosekund, které uběhly od půlnoci 1. ledna roku 0001 (čtete správně). A že jich k dnešnímu datumu pár je… Hodnota 635258601000000000 kupříkladu představuje 21.1.2014 0:15 UTC času. Hodnota nepočítá s „přestupnou sekundou“.
Musíme si uvědomit, že den coby naši srovnávací jednotku Excel vyjadřuje jako celé číslo a čas jako jeho desetinnou část. Výchozím datumem a tedy startovní čárou pro Excel je běžně 1.1.1900. Z online datumovek a z Visual Studia zjistíme, že časový úsek od 1.1.0001 do 1.1.1900 je velký 693595 dní (prostým výpočtem se k němu těžko dostaneme), čili 599266080000000000 ticks. Ve skutečnosti je tento počet o dva dny menší, tedy 693593 dní. Datum 1.1.1900 totiž v Excelu nepředstavuje nulu, ale jedničku a jak víme, Excel od počátku uvažuje jeden den navíc (chyba ve skutečnosti nepřestupného roku 1900).
1. Je-li údaj ticks v buňce A1, pak přepočet na sekundy, minuty, hodiny a nakonec dny provedeme následovně
=A1*100/(1000000000*60*60*24)
2. Odečteme dny do excelové „nuly“ a naformátujeme buňku jako „dd.mm.rr hh:mm:ss“
=A1*100/(1000000000*60*60*24)-693593
Výpočet se vztahuje k času UTC. Může být tedy nutné provést ještě korekci na časovou zónu (GMT). V tom případě číslo 693593 uvedené v rozdílu může být o zlomek (časový posun v hodinách dělený / 24) větší.
A jak na UNIX time, přesněji „UNIX epoch date and time stamp“?
Tento údaj představuje počet sekund uběhlých od půlnoci 1.1.1970 UTC času. Hodnota 1390268566 tak představuje náš čas 21.01.2014 02:42:46 (Tue Jan 21 2014 02:42:46 GMT+0100). Výpočet je jednodušší.
=DATUM(1970;1;1)+A1/(60*60*24)+(1/24)
V prvním kroku zjišťujeme počet dnů od 1.1.1900 do 1.1.1970 (na pozadí funkce DATUM vrací pořádové číslo), dále převádíme údaj UNIX času na dny od 1.1.1970 do současnosti a nakonec provedeme korekci na časovou zónu (+1 hodina). S chybou nepřestupného roku 1900 není třeba počítat.
