Jak przekonwertować numer seryjny programu Excel na datę i odwrotnie?

Skrypciarze odpowiadają na Wasze pytania

Witamy w rubryce TechNet, w której Skrypciarze z firmy Microsoft odpowiadają na częste pytania dotyczące używania skryptów w administracji systemu. Jeśli macie jakieś pytania z tej dziedziny, zachęcamy do wysłania e-maila na adres: scripter@microsoft.com. Nie możemy zagwarantować odpowiedzi na każde otrzymane pytanie, ale staramy się jak możemy.

Jak przekonwertować numer seryjny programu Excel na datę i odwrotnie?

Cześć, Skrypciarze! Mam arkusz, w którym pojawiają się następujące cyfry: 31738. Jak wiecie zapewne, to nie są zwykłe cyfry, odpowiadają bowiem dacie 11/22/1986 (w formacie europejskim: 22.11.1986). Jak przekonwertować je na datę i, jeżeli zajdzie potrzeba, przekonwertować tę datę z powrotem na numer seryjny?

-- TC

Cześć, TC! Nie wiem jak Ciebie, ale mnie w dniu dzisiejszym irytują głównie dwie rzeczy, mianowicie – kapelusze i „wypasione samochody”. Nie to, żeby nie było mnie stać na kapelusz tudzież taki samochód. Nie chcę również obrażać Ciebie, jeżeli okaże się, że akurat jeździsz takim samochodem, czasem nawet w kapeluszu… Chodzi o to, że ręce mi opadają, jak widzę przed sobą jakieś super BMW, które nie jest w stanie wyprzedzić jadącego 40 na godzinę autobusu… na dwupasmówce oczywiście. Ponadto, ręce mi opadają jeszcze niżej, jak jadę za kimś w kapeluszu, kto na pustej drodze jedzie 40 na godzinę i zmienia pasy co 5 sekund (oczywiście bez kierunkowskazu). Ręce już niżej opaść nie mogą, jeżeli te dwie osoby to jedna i ta sama osoba… Jak wytłumaczyć potem szefowi albo Pani Redaktor moje spóźnienie? Aż strach się przyznać. Mam jednak nadzieję, TC, że nawet jeśli jeździsz w kapeluszu, to jesteś w stanie wykonać jakże trudny manewr wyprzedzania (używając oczywiście czegoś tak passe, jak kierunkowskazu) i właśnie dlatego podzielę się z Tobą sekretną wiedzą na temat manewrowania w programie Excel, w szczególności zaś w obrębie konwertowania numerów seryjnych na daty i odwrotnie. Proszę zapiąć pasy:

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True



Set objWorkbook = objExcel.Workbooks.Add

Set objWorksheet = objWorkbook.Worksheets(1)



objExcel.Cells(1,1) = 31738

Wscript.Echo objExcel.Cells(1,1)



objExcel.Cells(1,1).NumberFormat = "m/d/yyyy"

Wscript.Echo objExcel.Cells(1,1)



objExcel.Cells(1,1).NumberFormat = ""

Wscript.Echo objExcel.Cells(1,1)

Zanim zajmiemy się szczegółami, omówimy pokrótce sposób operowania datami w programie Excel. Czas dla tego programu rozpoczął się nie tak dawno, bo 1 stycznia 1900 roku. Szkoda, gdyby zaczął się rok wcześniej, to byliby równolatkami z naszą Panią Redaktor. Nie udało się jednak i niestety dniem 1. w rozrachunku excelowym jest 01.01.1900. Dniem 2. jest oczywiście, jak się domyślacie, 02.01.2007, a 22.10.2007 – dniem 39377. Jeżeli kiedykolwiek importowaliście dane do programu Excel, to może zauważyliście, że daty pokazane są jako liczby (numery seryjne). Dziwne, ale prawdziwe.

Jak więc przekonwertować taką datę na numer i vice-versa? Przez nic innego jak nadanie różnych wartości do właściwości komórki NumberFormat.

Za chwilę wytłumaczymy wszystko.

Teraz jednak zacznijmy od początku (chociaż i tak zaczynamy od środka). Skrypt zaczyna się od wystąpienia obiektu Excel.Application i ustawienia jego właściwości Visible na True; to daje nam uruchomione i widoczne na ekranie wystąpienie programu Excel. Następnie używamy dwóch poniższych wierszy kodu i tworzymy nowy skoroszyt, po czym łączymy się z pierwszym arkuszem w skoroszycie:

Set objWorkbook = objExcel.Workbooks.Add

Set objWorksheet = objWorkbook.Worksheets(1)

Każdy, kto korzystał ze skryptów do obsługi programu Excel, widział taki kod miliony razy. Jeżeli jednak jeszcze nie używaliście takiego skryptu i jest to Wasz pierwszy kontakt, to już można to sobie wpisać do CV.

Po naniesieniu poprawek do CV nadajemy komórce A1 (rząd 1, kolumna 1) wartość 31738:

objExcel.Cells(1,1) = 31738

Aby się upewnić, że A1 rzeczywiście równa się 31738, używamy następującego wiersza kodu, który przywołuje echo wartości komórki:

Wscript.Echo objExcel.Cells(1,1)

Co otrzymujemy po wywołaniu takiego echa? Oczywiście wartość komórki A1, czyli nic innego, niż:

31738

Teraz dopiero rozpoczynamy prawdziwe manewry, czyli konwertowanie 31738 na datę. Można, a nawet trzeba, zrobić to w następujący sposób:

objExcel.Cells(1,1).NumberFormat = "m/d/yyyy"

Przypisując wartość m/d/yyyy , zmieniamy tu wartość właściwości komórki NumberFormat. Używamy tej właściwości w celu ustalenia jak dane wyświetlane są w arkuszu, chcemy teraz, aby nasze dane były wyświetlane jako data, używając formatu miesiąc (m) / dzień (d) / rok (yyyy). Jeżeli zastanawiacie się, co dokładnie oznacza nazwa „NumberFormat”, to przestańcie się zastanawiać natychmiast, ponieważ jest dość myląca, można bowiem używać tej właściwości do formatowania takich wartości jak liczby, daty, ułamki, kody… w sumie wszystko, co Wam przyjdzie na myśl. Można nawet tworzyć niestandardowe formaty; jeśli to Was interesuje, zajrzyjcie do archiwalnego artykułu z serii Cześć, Skrypciarze, tam znajdziecie przykłady.

Uwaga: W celu uzyskania dokładniejszych informacji, uruchomcie program Excel i kliknijcie menu Formatuj, a następnie Komórki. (W programie Excel 2007 należy kliknąć przycisk Format na wstążce Narzędzia główne i wybrać polecenie Formatuj komórki.) W zakładce Liczby okna dialogowego Formatuj komórki należy następnie kliknąć kategorię Niestandardowe. Widzicie wszystkie te symbole w oknie dialogowym? Wszystkich ich możecie używać ile Wam się żywnie podoba w celu modyfikowania właściwości NumberFormat.

Teraz zobaczcie, co się stanie jak przekonwertujemy z powrotem wartość komórki A1:

11/22/1986

Super, nie?

Super może być także w drugą stronę, obiecałem przecież, ze pokażę, jak się konwertuje datę na numer seryjny. Robimy to poprzez ustawienie wartości właściwości NumberFormat na pusty ciąg (mówiąc programistycznie, ustawiamy format komórki na „General”):

objExcel.Cells(1,1).NumberFormat = ""

Czy to rzeczywiście przekonwertuje nam datę na liczbę? Żeby się upewnić, przywołajmy echo wartości komórki A1:

31738

Co, niedowiarki, dało radę? Innymi słowy, zaczęliśmy od komórki A1 równej 31738 i teraz, po tych mrożących krew w żyłach i czasochłonnych manewrach otrzymaliśmy komórkę A1 równą 31738. To chyba najlepsza metafora ilustrująca naszą pracę i możliwości awansu, jaką udało mi się wymyślić.

Mamy nadzieję, TC, że jest to w miarę zrozumiałe i potrafisz już wykonać swój Excelowi manewr nawet wstecz i to z zamkniętymi oczami. Pamiętaj jednak o naszych radach odnośnie kapelusza…

Uwaga od redaktora: Chciałabym ogłosić wszem i wobec, że nasz kolega ze Skrypciarskiej redakcji jeździ niczym innym jak Lexusem, i to w kapeluszu… Wiem, bo wielokrotnie siedziałam mu na ogonie nie mogąc wyprzedzić…

Do początku strony