piątek, 21 maja 2010

Obliczamy różnicę pomiędzy datami

Witam wszystkich miłośników Excela!

Dziś zajmiemy się datami w Excelu. Nauczymy się, jak obliczyć różnicę pomiędzy datami. Poznamy funkcje, które obliczą, ile pomiędzy dwiema datami upłynęło: lat, miesięcy, dni i dni roboczych.

Pobawimy się trochę datami. Może się to przydać też w biznesie. Za pomocą tych funkcji łatwo obliczymy dzień zapłacenia faktury czy dostarczenia towaru.

Przygotowałem tabelkę, w której obliczymy, ile czasu upłynęło od dnia moich narodzin (3. marca 1986 r.) do dzisiaj.

ile czasu upłynęło Excel

No to do dzieła!



Na początek jedna uwaga. W związku z tym, że Excel przy obliczeniach operuje na liczbach, wszystkie daty powinny być zapisane jako liczby.

Zacznijmy od lat. Użyjemy tutaj funkcji ROK. Funkcja ROK powoduje, że Excel przyporządkowuje odpowiedni rok dla konkretnej raty. Dzięki funkcji ROK możemy operować na latach pomiędzy 1900 a 9999.

Obliczamy ile lat upłynęło pomiędzy dwiema datami. Funkcja ROK przyporządkowuje dacie odpowiadający jej rok, więc wystarczy tylko odjąć lata od siebie. Funkcja będzie więc wyglądać w ten sposób:

=ROK(E4)-ROK(D4)

funkcja rok excel

Funkcja obliczy, że mam już 24 lata.

Obliczmy teraz, ile miesięcy upłynęło pomiędzy dwiema datami. Tu sprawa jest bardziej skomplikowana. Funkcja MIESIĄC co prawda oblicza, o ile miesięcy oddalone są dwie daty ale tylko w ramach jednego roku. Do obliczenia ilości miesięcy pomiędzy różnymi latami trzeba użyć małego triku - obliczmy, ile lat upłynęło i pomnóżmy wynik przez 12. Funkcja w naszym przypadku wyglądałaby następująco:

=(ROK(E7)-ROK(D7))*12+MIESIĄC(E7)-MIESIĄC(D7)

funkcja miesiąc excel

Mam już 290 miesięcy.

Ciekaw jestem, ile dni upłynęło od mojego urodzenia. Tu sprawa jest bardzo prosta. Wystarczy zwykłe odejmowanie. Funkcja to w naszym przypadku po prostu:

=E10-D10

ile dni excel

Mam 8845 dni!

Dzięki Excelowi możemy też obliczyć ilość dni roboczych pomiędzy dwiema datami. Excel odejmuje po prostu wszystkie soboty, niedziele. Samemu możemy zdefiniować sobie też święta, których w naszym przypadku jest za dużo. Do obliczenia dni roboczych używana jest funkcja NETWORKDAYS. Funkcja ma postać:

=NETWORKDAYS(data początkowa; data końcowa; [święta]

Daty świąt mogą być wypisane w osobnej tabelce, którą zaznaczamy przy tworzeniu funkcji. W naszym przypadku funkcja NETWORKDAYS będzie miała postać:

=NETWORKDAYS(D13;E13;0)

funkcja networkdays excel

Od mojego urodzenia minęło 6320 dni roboczych (a właściwie powszednich bo bez świąt).

Wszystkie nasze obliczenia są gotowe.

różnica pomiędzy datami

Excel dość często jest używany do obliczeń związanych z datami. Zachęcam każdego do poćwiczenia sobie tych funkcji w domu.

W kolejnych postach poznamy kolejne ciekawe możliwości Excela. Zapraszam do subskrypcji bloga Abc Excel. Dzięki temu będziesz na bieżąco.

18 komentarze:

Anonimowy pisze...

nie działają zasady

Ynwestor pisze...

Z formułami wszystko jest ok. Skoro u Ciebie nie działa, to pewnie wina złego formatowania komórek. W plikach do ściągnięcia umieściłem link, z którego możesz pobrać sobie mój arkusz, który na pewno działa. Pozdrawiam!

Anonimowy pisze...

Super, właśnie nie wiedziałam jak obliczyć dni robocze. Dzięki :)

Mateusz Koczwara pisze...

A co jeśli w dwóch kolumnach mam daty w formacie dd-mm, a w osobnej rok? Jak uwzględnić kolumnę z rokiem przy obliczaniu różnicy dziennej?

Ynwestor pisze...

Załóż wątek z tym pytaniem na forum http://abc-excel.foreo.pl Postaramy się pomóc. Aha - najlepiej doklej jeszcze plik, z którym masz problem.

Anonimowy pisze...

Witam.
Nie wie Pan jak obliczyć godziny?

Anonimowy pisze...

i minuty:(

Ynwestor pisze...

Proszę zadać pytanie na forum. Postaramy się pomóc :)

Anonimowy pisze...

A u mnie nie działa funkcja NETWORKDAYS :/
Dlaczego?

Anonimowy pisze...

W jaki sposób w jednej komórce uzyskać liczbę dni pomiędzy danym dniem z przeszłości a datą bieżącą.

Zależy mi aby nie używać do tego celu dwóch dodatkowych komórek w których w jednej jest dana stała data z przeszłości a w drugiej funkcja zwracająca datę dzisiejszą. Przy użyciu tych dwóch dodatkowych komórek otrzymuję wynik bez problemu. Kiedy próbuję uzyskać wynik za pomocą jednej formuły w jednej komórce doznaję porażki. Wydaje się to proste a jednak mi nie wychodzi.

Silvi pisze...

Witam

Chciałabym sie dowiedziec, jak mozna dokladniej obliczyc wiek. Wg powyzszej formuly mozna obliczyc lata tylko wg rocznika. Formula nie jest precyzyjna, jezeli np ktos sie urodzil 01.02.1985, a mamy date np. 01.01.2012, to excel policzy 27 lat. Natomiast Osoba ta ma jeszcze 26, poniewaz dopiero w dzien urodzin, czyli 01.02.2012 bedzie miec 27. Zatem excel oblicza rocznikowo.
Czy mozna bardziej szczegolowo obliczyc (takze wg miesiecy), ile osoba ma lat?
z gory dziekuje za pomoc.

Pete pisze...

a jak policzyć ile minęło godzin pomiędzy dwoma rożnymi dniami np 10:13 21/02/2012 a 22.21 25/03/2012
a może godzin i minut :)

Anonimowy pisze...

Jutro mam sprawdzian i bardzo mi to pomogło, dziękuję !

Anonimowy pisze...

PO CO WSTAWIASZ LINK SKORO NIE DZIAŁA

Anonimowy pisze...

Skoro o datach mowa, czy istnieje możliwość, aby Excel w dwóch różnych komórkach podawał tę samą datę, raz w formacie polskim (np. 27.07.2012 lub 2012-07-27) a raz w formacie amerykańskim (2012/27/07 lub July 27th, 2012)?
Meridian

Anonimowy pisze...

skoro już o datach mowa, to jest jeszcze ukryta funkcja DATA.RÓŻNICA - sporo szczegółów w internecie, a zero w pomocy z Office :)

Do kolegi wyżej - bardzo prosto, wystarczy wejść we właściwości komórki (Ctrl-1) i zmienić ustawienia regionalne (lokalizacja) dla tej konkretnej komórki. Przy okazji - format z kropkami 27.07.2012 jest niemiecki, a nie polski (w polskich normach są tylko minusy)

Anonimowy pisze...

no i brakuje oczywiście informacji, że NETWORKDAYS zachowuje się różnie w zależności od wersji Excela.
Do wersji 2003 jest to w Analysis tool pack (trzeba doinstalować), w wersji 2007 działa OK, ale już w wersji 2010 trzeba uważać - bo przy otwarciu starego dokumentu z rozszerzeniem XLS nazwy są automatem zmieniane na polskie DNI.ROBOCZE (i po zapisie nie działają wstecznie na starszych wersjach). Ale na to też jest sposób :)

Anonimowy pisze...

Proszę, proszę, proszę - LICZBA nie ilość. Tam gdzie się da coś policzyć używajcie słowa liczba.

Prześlij komentarz

Uwaga: tylko uczestnik tego bloga może przesyłać komentarze.