fbpx

Jak ustalić ostatni dzień miesiąca?

18.01.2016 | Daty i czas, ECP2, Księgowość, Triki, Urlopy, Wynagrodzenie

Hehe, no właśnie. Niedawno się nad tym głowiłam, a to taka prosta sprawa (choć nie na pierwszy rzut oka). Potrzebowałam tego w wielu sytuacjach: liczenie średnich kosztów z określonych miesięcy, uniwersalny grafik czasu pracy, w którym chciałam, aby na podstawie podania miesiąca, wyświetlały się wszystkie dni danego miesiąca i takie tam.

Pierwszy dzień miesiąca łatwo ustalić: zawsze dzień = 1. Z ostatnim dniem jest gorzej, ponieważ miesiące mają różną liczbę dni, a luty to już w ogóle (właśnie, ile dni ma w tym roku? Odpowiedź na końcu artykułu:))!

Dziś pokażę wam 2 sposoby na rozwiązanie tego problemu. Oczywiście jest ich więcej, jednak moje są dość łatwe. Zobaczycie 🙂

Będę chciała ustalić pierwsze dni następujących miesięcy (na formatce):

Ostatni dzień miesiąca formatka

Formatka

Tak, jak pisałam, pokażę wam 2 metody na wskazanie ostatniego dnia miesiąca. Obie są bardzo podobne – wybierzcie sobie tę, która wam bardziej przypadnie do gustu. Ponieważ obie oparte są o funkcję DATA, omówię ją teraz krótko.

Funkcja DATA

Funkcja ta jest megaprosta i megaprzydatna. Ma 3 argumenty, takie, jak każda data ma, czyli:

  1. Rok,
  2. Miesiąc,
  3. Dzień.

Przykładowo taki zapis:

=DATA(2016;01;19)

zwróci na taką datę: 2016-01-19. Oczywiście argumentami tej funkcji mogą być odwołania do komórek jak i funkcje zwracające liczby. Co potrzeba.

Wynikiem funkcji jest data, czyli tak na prawdę liczba naturalna. Przypomnę, że dla Excela daty to są liczby, a pierwsza data, jaką Excel (standardowo) rozpoznaje to 1900-01-01 i jest to liczba 1. Wspomniana data 2016-01-19 jest więc liczbą 42 388. Funkcja DATA zwraca takie właśnie liczby, tylko sformatowane od razu na daty.

Jeśli zatem chcemy ustalić pierwszy dzień  lutego 2016, wystarczy wpisać tak:

=DATA(2016;2;1)

Zawsze więc wpiszemy rok i miesiąc, które nas interesują i jako ostatni argument – dzień – wpiszemy po prostu 1. Cała filozofia.

Ok, tyle tytułem wstępu, przejdźmy do ustalania ostatniego dnia miesiąca.

Metoda 1

Jedną z metod ustalania ostatniego dnia miesiąca jest ustalenie pierwszego dnia kolejnego miesiąca i… odjęcie 1. !!! Tyle 🙂 Pokazałam przed chwilą, jak ustalić pierwszy dzień miesiąca – banalnie. Odejmij 1 i masz wynik 🙂

Przykładowo chcę ustalić ostatni dzień sierpnia roku 2016. Muszę więc poznać pierwszy dzień września 2016 i odjąć od tego 1. Czyli:

=DATA(2016;9;1)-1

Funkcja zwróci mi datę 2016-09-01, czyli liczbę 42 614. Po odjęciu 1 otrzymam liczbę 42 613, czyli dzień wcześniej, czyli 2016-08-31.

Tadam!

To jedźmy do drugiej metody.

Metoda 2

Druga metoda również korzysta z funkcji DATA. Tutaj jednak wykorzystamy pewną właściwość tej funkcji.

Mianowicie, jeśli w ostatnim argumencie (dzień) wpiszemy dzień z danego miesiąca, to oczywiście funkcja zwróci nam datę z tym dniem. Co jednak, gdy np. dla marca 2015 wpiszemy tam liczbę 32? Tak, Excel zwróci nam kolejną datę, czyli 2015-04-01.

Idąc tym tropem, co będzie, gdy dla marca 2015 wpiszemy tam 0 (zero)? Właśnie tak – otrzymamy dzień poprzedni, czyli 2015-02-28.

I to jest druga metoda. Czyli:

=DATA(2015;3;0)

Zerowy dzień następnego miesiąca.

Parametryzowanie

Wracając do moich przykładów z formatki, tak wyglądają wyniki:

Ostatni dzień miesiąca uzupełnione

Wyniki

Można to oczywiście parametryzować. Skoro w kolumnie A mam wpisane miesiące, których ostatnie dni chcę poznać, nie będę przecież klepała 8 funkcji ręcznie. Odwołam się do komórek w kolumnie A i użyję odpowiednich funkcji do ustalenia odpowiedniego roku i miesiąca. Aby to zadziałało, w kolumnie A muszę mieć oczywiście daty, czyli de facto liczby. To, że daty te wyświetlają się jako nazwa miesiąca i rok, to już kwestia formatowania niestandardowego.

Jak więc mamy liczby w kolumnie A, możemy działać z funkcjami.

I tak, metoda 1., czyli odejmowanie 1 od pierwszego dnia kolejnego miesiąca można zapisać tak (komórka B4):

=DATA(ROK(A4);MIESIĄC(A4)+1;1)-1

W powyższej formule rok ustalany jest funkcją ROK, która pobiera dane z komórki A4. Miesiąc ustalany jest funkcją MIESIĄC, która również pobiera dane z komórki A4. Dzień jest to zawsze 1.

Dla metody 2. natomiast, wystarczy wpisać w C4 następującą formułę:

=DATA(ROK(A4);MIESIĄC(A4)+1;0)

Tutaj w drugim argumencie – miesiąc – dodajemy 1, aby uzyskać miesiąc następny. Dzień to zawsze 0 (zero).

Jak widzicie – metody są bardzo podobne i bazują na tej samej logice. Ostatni dzień miesiąca jest przed pierwszym dniem następnego miesiąca.

 

A odpowiedź na pytanie z początku wpisu to: w tym roku luty ma oczywiście 29 dni 🙂

I jak? Przyda wam się? Jestem ciekawa do czego tego użyjecie. Koniecznie napiszcie w komentarzach.

Jeżeli chcesz lepiej poznać Excela na poziomie średniozaawansowanym zapisz się na listę zainteresowanych kursem Excel w codziennej pracy cz. 2! Podczas oczekiwania na kurs będę Ci wysyłała informacje, ciekawostki i excelowe triki. Bezpłatnie! 

Pomogłam Ci? Postaw mi wirtualną kawę! →

Przeczytaj podobne wpisy

Kategorie

6 komentarzy

  1. Aby uzyskać ostatni dzień miesiąca wystarczy do komórki np. A1 wpisać datę 01.01.2018. W dowolnej komórce wpisać formułę „EOMONTH(A1,0) i gotowe. A co najważniejsze nie trzeba zastanawiać się ile w danym roku luty ma dni

    Reply
    • Pewnie, super pomysł! Dzięki za ten komentarz 🙂

      Reply
  2. Niestety Excel ma pewien problem z latami przestępnymi.
    Proszę sprawdzić wynik dla roku 1900. Wg. Excela (1900/03/01)-1=(1900/02/29).

    Dla większości z nas nie ma to znaczenia, ale jak ktoś zajmuje się analizą historyczną to już może być pewien problem. 😀

    Reply
    • Tak… ten błąd na szczęście występuje tylko w 1900 roku i jest z nami w Excelu tylko po to, żeby zachować spójność z Lotusem… (którego nawet na oczy nigdy nie widziałam, takie to stare ;)).
      Dziękuję za cenny komentarz!

      Reply
  3. Cześć, a jak zrobić by taki ostatni dzień miesiąca był zaznaczany poprzez formatowanie warunkowe? Mam daty w formacie dd.mm.rrrr.

    Reply
    • Hej, przychodzi mi do głowy sprawdzenie każdej daty czy jak się doda do niej 1, to dzień od nowej daty będzie = 1. Jeśli tak – data jest ostatni dniem miesiąca.

      Reply

Leave a Reply to Jan Jegerski Anuluj pisanie odpowiedzi

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *

Pin It on Pinterest