6.4 lecke · 4/4 · kb. 6 perc
Dátum- és időfüggvények
A dátum- és időfüggvények két irányba dolgoznak: szétszedik a dátumot évre, hónapra, napra, vagy éppen ezekből raknak össze új dátumot. Ebben a leckében gyakorlati példákon keresztül ismered meg őket.
Dátum- és időegységek kinyerése
Hat függvény ad vissza egy-egy egységet a dátumból vagy az időből. Mindegyiknek egyetlen argumentuma van: egy dátum vagy idő, amely lehet cellahivatkozás, képlet vagy idézőjeles állandó.
| Dátumból | Eredmény | Időből | Eredmény |
|---|---|---|---|
ÉV (YEAR) | az év száma | ÓRA (HOUR) | az óra (0-23) |
HÓNAP (MONTH) | a hónap száma (1-12) | PERCEK (MINUTE) | a perc (0-59) |
NAP (DAY) | a hónap napja (1-31) | MPERC (SECOND) | a másodperc (0-59) |
Ha az A2 cellában a 2020.12.17 8:38:47 időpont áll, akkor =ÉV(A2) eredménye 2020, =HÓNAP(A2) eredménye 12, =NAP(A2) eredménye 17, =ÓRA(A2) eredménye 8, =PERCEK(A2) eredménye 38, =MPERC(A2) eredménye 47.
Jó tudni
A hat név közül öt egyes számú, de a perc függvénye többes számban áll: PERCEK. Gyakori elírás a „PERC”, amire #NÉV? hibát kapsz.
A hét napját a HÉT.NAPJA (WEEKDAY) adja. Ha második argumentumnak 2-t adsz meg, a hétfő lesz az 1, a vasárnap a 7: =HÉT.NAPJA("2025.03.10";2) eredménye 1, mert ez a nap hétfő. Így például könnyen megjelölheted a hétvégéket: =HÉT.NAPJA(A2;2)>5.
Életkor, negyedév, eltelt hónapok
Életkor a születési dátumból
Kollégáid életkorát szeretnéd folyamatosan követni. Első ötlet: a mostani évből kivonod a születési évet. Ez azonban addig, amíg az illető idei születésnapja el nem jött, egy évvel többet mutat.
Pontosabb, ha a megélt napok számát elosztod 365,25-tel (a négyévenkénti szökőnap miatt), és a hányados egész részét veszed. Az egész részt a KVÓCIENS (QUOTIENT) függvény adja: két argumentuma az osztandó és az osztó.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | név | született | életkor (év) | életkor (nap) |
| 2 | Székely Katinka | 1995.01.04 | 30 | 30 |
| 3 | Szakács Leonóra | 1981.03.15 | 44 | 43 |
| 4 | Serfőző Ábrahám | 1997.02.18 | 28 | 28 |
| 5 | Csordás Bíborka | 1993.03.11 | 32 | 31 |
| 6 | Pázmány Liliána | 1980.09.06 | 45 | 44 |
Az eredmények 2025.03.10-én érvényesek. C2: =ÉV(MA())-ÉV(B2), D2: =KVÓCIENS(MA()-B2;365,25)
A kiemelt soroknál a két módszer eltér: Leonóra, Bíborka és Liliána idei születésnapja még nem jött el, ezért a D oszlop a helyes.
Figyelem
A 365,25-tel való osztás közelítés. A születésnap környékén egy napot tévedhet: aki pontosan ma tölti a 25. évét, annál a képlet még 24-et mutathat. Kimutatásokhoz ez általában elég, de hivatalos számításhoz (például jogosultság) ellenőrizd a határeseteket.
Félév és negyedév
A hónap számából egyszerű osztással megkapod, melyik negyedévbe esik egy dátum: a hónapot elosztod 3-mal, és felfelé egészre kerekítesz. A KEREK.FEL (ROUNDUP) második argumentuma, a 0 jelenti az egészre kerekítést.
A félévhez 6-tal osztasz. Ha római számmal és ponttal szeretnéd látni (I., II.), a RÓMAI (ROMAN) függvény és az & összefűzés segít:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | azonosító | dátum | félév | negyedév |
| 2 | 0001 | 2021.02.25 | I. | 1 |
| 3 | 0002 | 2021.05.13 | I. | 2 |
| 4 | 0003 | 2020.11.13 | II. | 4 |
| 5 | 0004 | 2019.08.14 | II. | 3 |
Május: 5/3 = 1,67 → 2. negyedév; 5/6 = 0,83 → I. félév.
Teljes naptári hónapok két dátum között
Hány teljes naptári hónap van a kezdő és a záró dátum hónapja között? A két dátum évét és hónapját használod: az évek különbségét 12-vel szorzod, hozzáadod a hónapok különbségét, majd levonsz 1-et (a kezdő és a záró hónap nem teljes).
Ha a kezdés 2021.02.25., a befejezés 2024.02.29., akkor: (2024 - 2021) × 12 + 2 - 2 - 1 = 35. Ez a 2021. márciustól 2024. januárig tartó 35 hónap.
Dátum összerakása: DÁTUM és IDŐ
DÁTUM(év; hónap; nap)IDŐ(óra; perc; másodperc)A DÁTUM (DATE) három számból rak össze dátumot, az IDŐ (TIME) három számból időt. Első ránézésre nem látszik, mire jók. A titok az, hogy mindig érvényes eredményt adnak: a „túlcsorduló” értékeket átváltják.
| Képlet | Eredmény | Mi történt? |
|---|---|---|
=DÁTUM(2000;35;1) | 2002.11.01 | 35 hónap = 2 év és 11 hónap |
=DÁTUM(2000;0;1) | 1999.12.01 | a 0. hónap az előző év decembere |
=DÁTUM(2000;-5;1) | 1999.07.01 | decembertől még 5 hónappal vissza |
=DÁTUM(2000;1;35) | 2000.02.04 | január 31 napja után még 4 nap |
=DÁTUM(2000;1;0) | 1999.12.31 | a 0. nap az előző hónap utolsó napja |
=DÁTUM(48;1;1) | 1948.01.01 | az 1900 alatti évszám 1900-hoz adódik |
=IDŐ(8;75;0) | 9:15 | 75 perc = 1 óra 15 perc |
Negatív évszámra #SZÁM! hibát kapsz. Az IDŐ eredménye mindig egy napnál rövidebb: =IDŐ(25;0;0) eredménye 1:00.
Példa: lejárat hónapokban megadott futamidővel
Egy bank lekötött betéteinek lejáratát kell kiszámolnod. A futamidő hónapokban van megadva, ezért nem adhatsz egyszerűen napokat a dátumhoz. Szedd szét a dátumot, add hozzá a hónapokat a hónap számához, és a DÁTUM rakja össze:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | számla | lekötés | hónap | lejár |
| 2 | 8965-37-6 | 2024.10.24 | 36 | 2027.10.24 |
| 3 | 1391-47-3 | 2025.03.26 | 24 | 2027.03.26 |
| 4 | 6612-85-8 | 2024.08.31 | 6 | 2025.03.03 |
A D2 cella képlete: =DÁTUM(ÉV(B2);HÓNAP(B2)+C2;NAP(B2))
A 36 hónap a hónap számát 10-ről 46-ra növeli, amit a DÁTUM 3 évre és 10 hónapra vált. Ugyanígy működik az évekkel is: ha egy elítélt büntetése 2 év 8 hónap, a szabadulás napja =DÁTUM(ÉV(B3)+C3;HÓNAP(B3)+D3;NAP(B3)).
Figyelem: hónap végi dátumok
A 6612-85-8-as számlánál a 2024.08.31. + 6 hónap „2025. február 31.” lenne. Ilyen nap nincs, ezért a DÁTUM továbbszámol: február 28 napja után még 3 nap, így 2025.03.03. lesz az eredmény. Hónap végén induló futamidőknél ezt mindig ellenőrizd.
Számolás idővel: fizetés megkezdett egységekre
Napi fizetést kell kiszámolnod a kezdés és a befejezés ismeretében: minden megkezdett 45 percért 7 000 Ft jár. Nem kell perceket számolnod az ÓRA és a PERCEK függvénnyel: az időt közvetlenül eloszthatod egy idézőjeles időállandóval.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | név | kezdés | befejezés | fizetés |
| 2 | Bognár Zoltán | 8:27 | 15:50 | 70 000 |
| 3 | Varga Tímea | 8:29 | 12:17 | 42 000 |
| 4 | Juhász Edit | 9:20 | 14:38 | 56 000 |
A D2 cella képlete: =(KVÓCIENS(C2-B2;"0:45")+1)*7000
Bognár Zoltán 7 óra 23 percet, vagyis 443 percet dolgozott. Ebben a 45 perc 9-szer van meg egészen, és a 10. egységet is megkezdte: 10 × 7 000 = 70 000 Ft.
Tipp
Ha a munkaidő pontosan 45 perc többszöröse (például 90 perc), a „+1” egy egységgel többet számol. Ha ezt el akarod kerülni, számolj egész percekkel és kerekíts felfelé: =KEREK.FEL(KEREKÍTÉS((C2-B2)*1440;0)/45;0)*7000.
Dátum és idő szövegből
A DÁTUMÉRTÉK (DATEVALUE) a szövegként megadott dátumot, az IDŐÉRTÉK (TIMEVALUE) a szövegként megadott időt alakítja számmá: =DÁTUMÉRTÉK("2024.03.15") eredménye 45 366. Ezekre főleg feltételekben lesz szükséged, ahol egy idézőjeles dátumot számként kell összehasonlítani. Erről a HA függvény leckéjében lesz szó.
Összefoglalás
ÉV,HÓNAP,NAP,ÓRA,PERCEK,MPERC: egy-egy egységet adnak vissza a dátumból vagy időből.- Életkor közelítőleg:
=KVÓCIENS(MA()-B2;365,25); a puszta évkülönbség születésnap előtt egy évvel többet mutat. - Negyedév:
=KEREK.FEL(HÓNAP(B2)/3;0). - A
DÁTUMés azIDŐa túlcsorduló értékeket átváltja, ezért hónapokkal is eltolhatsz egy dátumot:=DÁTUM(ÉV(B2);HÓNAP(B2)+C2;NAP(B2)). - A
DÁTUMÉRTÉKés azIDŐÉRTÉKszövegből készít dátumot, illetve időt.
Gyakorlás
Töltsd le a gyakorló munkafüzetet, és oldd meg a megadott munkalapok feladatait. A sárga cellákba kerülnek a képletek. Ha elakadsz, nézd meg a megoldást.
Dátum és idő: gyakorló feladatok
- Életkor Életkor napokban, születési év és hónap, a hét napja és az idén betöltött életkor.
Ez volt a modul utolsó leckéje
Ellenőrizd a tudásodat a modul rövid kvízével: 6 kérdés, azonnali visszajelzéssel.