Zliczanie dni między datami

Jednym z najczęstszych zadań związanych z datami jest określenie, ile czasu dzieli dwie daty: liczby dni do terminu, wieku w latach lub długości projektu.

Ponieważ arkusze kalkulacyjne przechowują daty jako liczby, najprostszym rozwiązaniem jest zwykłe odejmowanie. W bardziej zaawansowanych przypadkach, takich jak liczenie pełnych miesięcy lub lat, przydaje się funkcja DATEDIF.

W tej lekcji opanują Państwo zarówno szybkie odejmowanie, jak i precyzyjne podejście z użyciem funkcji DATEDIF.

Aby policzyć dni między wcześniejszą datą w komórce A2 a późniejszą datą w komórce B2, wystarczy je od siebie odjąć.

Wynikiem jest liczba dni między tymi datami. Jeśli A2 zawiera 1 czerwca, a B2 — 18 czerwca, formuła =B2-A2 zwróci 17.

Późniejszą datę należy umieścić jako pierwszą, aby wynik był dodatni. Odjęcie dat w odwrotnej kolejności daje liczbę ujemną, co oznacza po prostu odwrócenie kolejności.

Częsta niespodzianka: po odjęciu dat komórka z wynikiem czasami wyświetla datę zamiast liczby, na przykład 1/17/1900.

Dzieje się tak, ponieważ komórka odziedziczyła format daty. Obliczenie jest prawidłowe, a problem dotyczy tylko sposobu wyświetlania. Aby go usunąć, należy zmienić format komórki na Liczbowe i ustawić zero miejsc po przecinku.

Po sformatowaniu jako liczby wyświetli się oczekiwana, czytelna liczba dni.

Zwykłe odejmowanie liczy odstępy między dniami, a nie same dni. Od 1 do 18 czerwca odejmowanie daje wynik 17, ale po uwzględnieniu obu końców otrzymujemy 18 dni kalendarzowych.

Jeśli w danej sytuacji należy uwzględnić zarówno dzień początkowy, jak i końcowy, trzeba dodać do wyniku 1.

W ten sposób formuła =B2-A2+1 zwraca liczbę dni z uwzględnieniem obu końców. Należy zdecydować, której wartości wymaga raport.

W przypadku liczenia pełnych miesięcy lub lat zwykłe odejmowanie jest niewygodne. Służy do tego funkcja DATEDIF. Przyjmuje trzy argumenty: datę początkową, datę końcową i kod jednostki ujęty w cudzysłowy.

Kod jednostki określa, co będzie liczone:

• "D" liczy dni
• "M" liczy pełne miesiące
• "Y" liczy pełne lata

Uwaga: DATEDIF to ukryta funkcja starszego typu. Działa poprawnie, ale nie jest wyświetlana na liście autouzupełniania w Excelu.

Jednostka "M" służy do liczenia pełnych miesięcy między dwiema datami. Niepełne miesiące są pomijane.

Jeśli A2 zawiera 15 stycznia, a B2 — 10 kwietnia, formuła =DATEDIF(A2, B2, "M") zwróci 2, ponieważ upłynęły tylko dwa pełne miesiące. Trzeci miesiąc nie zakończył się jeszcze dziesiątego dnia.

Dokładnie tak zwykle mierzy się staż subskrypcji lub długość umowy.

Jednostka "Y" zwraca pełne lata, dlatego jest właściwym sposobem obliczania wieku.

Jeśli A2 zawiera datę urodzenia, porównanie jej z dzisiejszą datą podaje wiek osoby w pełnych latach. Dopóki urodziny nie przypadną w bieżącym roku, funkcja prawidłowo zwraca niższą wartość.

Pozwala to uniknąć błędów zaokrąglania, które pojawiają się przy dzieleniu różnicy dni przez 365.

Funkcja DATEDIF udostępnia dodatkowe kody jednostek, które pozwalają rozbić okres na części:

• "YM" = miesiące pozostałe po odliczeniu pełnych lat
• "MD" = dni pozostałe po odliczeniu pełnych miesięcy
• "YD" = dni pozostałe po odliczeniu pełnych lat

Dzięki nim można przedstawić czas trwania jako 2 lata, 3 miesiące, 5 dni, łącząc trzy wywołania funkcji DATEDIF.

Zwykłe odejmowanie liczy każdy dzień kalendarzowy, w tym weekendy. Aby policzyć tylko dni robocze, należy użyć funkcji NETWORKDAYS.

Liczy ona dni od poniedziałku do piątku między dwiema datami i pozwala podać listę pomijanych świąt. Dlatego formuła =NETWORKDAYS(A2, B2) zwraca liczbę dni od poniedziałku do piątku, a opcjonalny trzeci argument wskazuje zakres świąt.

To właściwe narzędzie do szacowania terminów dostaw i tworzenia harmonogramów SLA.

Załóżmy, że termin płatności faktury znajduje się w komórce A2. Należy oznaczyć, ile dni minęło od tego terminu do dziś, ale wyświetlać 0, jeśli termin jeszcze nie upłynął.

Trzeba odjąć termin płatności od dzisiejszej daty, a następnie użyć funkcji MAX z wartością 0, aby faktura, której termin jeszcze nie upłynął, nigdy nie wyświetlała liczby ujemnej.

Ta pojedyncza formuła zapewnia czytelne i sensowne obliczenie liczby dni opóźnienia w raporcie należności.

Mając liczbę dni, można przeliczyć ją na inne jednostki. Aby wyrazić okres w pełnych tygodniach, należy podzielić różnicę dni przez 7 i zaokrąglić wynik w dół za pomocą funkcji INT.

Formuła =INT((B2-A2)/7) podaje, ile pełnych tygodni mieści się między dwiema datami. Różnica 17 dni daje 2 tygodnie.

Ta sama zasada działa dla dowolnej jednostki: należy podzielić liczbę dni i zaokrąglić wynik zgodnie z potrzebami raportu.

Sprawdź swoją wiedzę o różnicach między datami.

Poznali Państwo kilka sposobów mierzenia okresów:

• =B2-A2 podaje różnicę dni (późniejszą datę należy umieścić jako pierwszą); dodanie 1 pozwala uwzględnić oba końce.
• Jeśli w komórce z wynikiem wyświetla się data, należy ponownie ustawić jej format na Liczbowe.
• DATEDIF(start, end, "D"/"M"/"Y") liczy pełne dni, miesiące lub lata; świetnie nadaje się do obliczania wieku i stażu.
• NETWORKDAYS liczy dni robocze i może pomijać święta.
• Zastosowanie MAX(0, ...) pozwala uniknąć ujemnej liczby dni opóźnienia.

Kurs dotyczący funkcji daty i czasu został ukończony.