Rumus Kalkulasi Tanggal Untuk Berbagai Keperluan

Saya akan "all out" kali ini, memberikan berbagai rumus untuk berbagai keperluan yang berkaitan kalkulasi menggunakan tanggal, langsung saja satu persatu dibawah ini:

KASUS A:Menghitung Jarak Antara 2 Tanggal
1. Dengan DATEDIF:
=IF(DATEDIF(A4;B4;"y")=0;"";DATEDIF(A4;B4;"y")&" tahun ")&IF(DATEDIF(A4;B4;"ym")=0;"";DATEDIF(A4;B4;"ym")&" bulan ")&IF(DATEDIF(A4;B4;"md")=0;"";DATEDIF(A4;B4;"md")&" hari")
2. Tanpa DATEDIF:
=TRIM(IF(INT((B4-A4)/365,25)=0;"";INT((B4-A4)/365,25)&" tahun")&" "&IF(INT(MOD((B4-A4)/365,25;1)*12)=0;"";INT(MOD((B4-A4)/365,25;1)*12)&" bulan")&" "&IF(INT(MOD((B4-A4)/30,44;1)*30,44)=0;"";INT(MOD((B4-A4)/30,44;1)*30,44)&" hari"))

Kasus B: Menghitung Umur
1. Dengan DATEDIF:
=IF(DATEDIF(A9;TODAY();"y")=0;"";DATEDIF(A9;TODAY();"y")&" tahun ")&IF(DATEDIF(A9;TODAY();"ym")=0;"";DATEDIF(A9;TODAY();"ym")&" bulan ")&IF(DATEDIF(A9;TODAY();"md")=0;"";DATEDIF(A9;TODAY();"md")&" hari")
2. Tanpa DATEDIF:
=TRIM(IF(INT((TODAY()-A9)/365,25)=0;"";INT((TODAY()-A9)/365,25)&" tahun")&" "&IF(INT(MOD((TODAY()-A9)/365,25;1)*12)=0;"";INT(MOD((TODAY()-A9)/365,25;1)*12)&" bulan")&" "&IF(INT(MOD((TODAY()-A9)/30,44;1)*30,44)=0;"";INT(MOD((TODAY()-A9)/30,44;1)*30,44)&" hari"))
atau
=IF(YEAR(DATE(YEAR(TODAY())-YEAR(A9);MONTH(TODAY())-MONTH(A9)+1;DAY(TODAY())-DAY(A9)+1))-1900=0;"";YEAR(DATE(YEAR(TODAY())-YEAR(A9);MONTH(TODAY())-MONTH(A9)+1;DAY(TODAY())-DAY(A9)+1))-1900&" Tahun ")&IF(MONTH(DATE(YEAR(TODAY())-YEAR(A9);MONTH(TODAY())-MONTH(A9)+1;DAY(TODAY())-DAY(A9)+1))-1=0;"";MONTH(DATE(YEAR(TODAY())-YEAR(A9);MONTH(TODAY())-MONTH(A9)+1;DAY(TODAY())-DAY(A9)+1))-1&" Bulan ")&IF(DAY(DATE(YEAR(TODAY())-YEAR(A9);MONTH(TODAY())-MONTH(A9)+1;DAY(TODAY())-DAY(A9)+1))-1=0;"";DAY(DATE(YEAR(TODAY())-YEAR(A9);MONTH(TODAY())-MONTH(A9)+1;DAY(TODAY())-DAY(A9)+1))-1&" Hari")

Kasus C: Rumus Untuk Mengetahui Hari Ulang Tahun pada Tahun Ini
Hari Ulang Tahun pada tahun ini adalah……
=TEXT(DATE(YEAR(TODAY());MONTH(A21);DAY(A21));"dddd")&", "&TEXT(DATE(YEAR(TODAY());MONTH(A21);DAY(A21));"dd mmm yyyy")

Rumus untuk menghitung sisa hari menjelang ulang tahun atau sudah lewat
=IF(TODAY()>DATE(YEAR(TODAY());MONTH(A21);DAY(A21));"Wah sudah lewat tuh...";IF(TODAY()=DATE(YEAR(TODAY());MONTH(A21);DAY(A21));"Selamat Ulang Tahun !!";"Kurang "&TRIM(IF(INT((DATE(YEAR(TODAY());MONTH(A21);DAY(A21))-TODAY())/365,25)=0;"";INT((DATE(YEAR(TODAY());MONTH(A21);DAY(A21))-TODAY())/365,25)&" tahun")&" "&IF(INT(MOD((DATE(YEAR(TODAY());MONTH(A21);DAY(A21))-TODAY())/365,25;1)*12)=0;"";INT(MOD((DATE(YEAR(TODAY());MONTH(A21);DAY(A21))-TODAY())/365,25;1)*12)&" bulan")&" "&IF(INT(MOD((DATE(YEAR(TODAY());MONTH(A21);DAY(A21))-TODAY())/30,44;1)*30,44)=0;"";INT(MOD((DATE(YEAR(TODAY());MONTH(A21);DAY(A21))-TODAY())/30,44;1)*30,44)&" hari"))&" lagi"))

Kasus D: Membuat Reminder dari Agenda Kegiatan/Peristiwa atau Masa Kadaluarsa SIM/KTP/STNK
Di Cell C31 dan dicopy kebawah:
=IF(B31="";"";IF(B31=TODAY();"Hari ini Acaranya/Jatuh Tempo";IF(B31>TODAY();IF(DATEDIF(TODAY();B31;"y")=0;"";DATEDIF(TODAY();B31;"y")&" tahun ")&IF(DATEDIF(TODAY();B31;"ym")=0;"";DATEDIF(TODAY();B31;"ym")&" bulan ")&IF(DATEDIF(TODAY();B31;"md")=0;"";DATEDIF(TODAY();B31;"md")&" hari")&" lagi";"Sudah lewat "&IF(DATEDIF(B31;TODAY();"y")=0;"";DATEDIF(B31;TODAY();"y")&" tahun ")&IF(DATEDIF(B31;TODAY();"ym")=0;"";DATEDIF(B31;TODAY();"ym")&" bulan ")&IF(DATEDIF(B31;TODAY();"md")=0;"";DATEDIF(B31;TODAY();"md")&" hari"))))

Kasus E: Menghitung Jarak Antara 2 Tanggal Dalam Minggu dan Hari:
=IF(TRUNC((B44-A44)/7)=0;"";TRUNC((B44-A44)/7)&" Minggu")&" "&IF(MOD((B44-A44);7)=0;"";MOD((B44-A44);7)&" Hari")
atau
=IF(INT((B44-A44)/7)=0;"";INT((B44-A44)/7)&" Minggu ")&IF(MOD(((B44-A44)/7);1)*7=0;"";MOD(((B44-A44)/7);1)*7&" Hari")
atau
=IF(INT(DATEDIF(A44;B44;"D")/7)=0;"";INT(DATEDIF(A44;B44;"D")/7)&" Minggu ")&IF(MOD(DATEDIF(A44;B44;"D");7)=0;"";MOD(DATEDIF(A44;B44;"D");7)&" Hari")

Kasus F: Rumus untuk mengetahui minggu ke berapa dari tanggal (dalam 1 bulan)
="Minggu ke "&WEEKNUM(A55)-WEEKNUM(A55-DAY(A55)+1)+1
 atau
 ="Minggu ke "&INT((DAY(A55)+6)/7)

Kasus G: Rumus untuk mengetahui jumlah hari dari suatu bulan
=DAY(DATE(YEAR(A63);MONTH(A63)+1;0))&" Hari"
atau
=DAY(EOMONTH(A21;0))&" Hari"

Atau kasus seperti ini, anda punya list nama-nama bulan:

 









Rumus untuk menghasilkan jumlah hari dari suatu bulan dengan kriteria nama bulan (teks):
=DAY(DATE(YEAR(TODAY());MONTH($A69&1)+1;0))&" Hari"

Kasus H: Konversi Angka Hari ke Tahun, ulan dan Hari
=IF(DATEDIF(366;366+A78;"y")=0;"";DATEDIF(366;366+A78;"y")&" tahun ")&IF(DATEDIF(366;366+A78;"ym")=0;"";DATEDIF(366;366+A78;"ym")&" bulan ")&IF(DATEDIF(366;366+A78;"md")=0;"";DATEDIF(366;366+A78;"md")&" hari ")

Kasus I: Bulan ini tinggal berapa hari lagi yaaa….?
="tinggal "&EOMONTH(TODAY();0)-TODAY()&" hari lagi"
atau
="tinggal "&DATE(YEAR(TODAY());MONTH(TODAY())+1;0) - TODAY()&" hari lagi"

Kasus J:
Yang satu ini mungkin untuk just for fun karena bisa untuk men-cek, hari dan tanggal gajian kita kurang berapa hari sih? Dan rumus ini saya buat otomatis akan men-skip hari "Sabtu" dan "Minggu". Seperti contoh diatas, pada cell A90 tertulis angka 1, rumus akan mengkonversi tanggal 1 tersebut adalah tanggal 1 Maret 2015 dan itu jatuh pada hari minggu, dan itu weekend, nah ndak mungkin toh terima gaji? Nah pasti mundur ke Senin. Ke-2 rumus di cell A92 dan A94(warna kuning) tidak saya tulis disini karena terlalu panjang, tetapi silahkan dilihat di file latihan.

Rumus untuk mencari hari dan tanggal terakhir Bulan Ini:
=TEXT(EOMONTH(TODAY();0);"dddd")&", "&TEXT(EOMONTH(TODAY();0);"dd mmmm yyy")
atau
=TEXT(DATE(YEAR(TODAY());MONTH(TODAY())+1;0);"dddd")&", "&TEXT(DATE(YEAR(TODAY());MONTH(TODAY())+1;0);"dd mmmm yyyy")

Rumus untuk mencari hari dan tanggal pertama Bulan ini:
=TEXT(EOMONTH(TODAY();0)+1;"dddd")&", "&TEXT(EOMONTH(TODAY();0)+1;"dd mmmm yyyy")
atau
=TEXT(DATE(YEAR(TODAY());MONTH(TODAY())+1;1);"dddd")&", "&TEXT(DATE(YEAR(TODAY());MONTH(TODAY())+1;1);"dd mmmm yyyy")

Rumus untuk cek tahun kabisat atau bukan:
=IF(DAY(DATE(A108;2;29))=29;"Tahun Kabisat";"Normal")
atau
=IF(MONTH(DATE(A108;2;29))=2;"Tahun Kabisat";"Normal")
atau
=IF(ISNUMBER(--(A108&"-02-29"));"Tahun Kabisat";"Normal")
atau
=IF(MOD(A108;4)=0;"Tahun Kabisat";"Normal")

Mudah2xan bermanfaat Wassalamualaikum Warahmatullahi Wabarakatuh

DOWNLOAD FILE LATIHAN

Mengambil Data Dengan Multiple Results

Saya pernah menulis posting tentang mengambil data dengan beberapa hasil atau multiple results..... saya ingin refresh materi itu kali ini. Saya ada beberapa contoh yang mungkin berguna untuk diterapkan dan dikembangkan sesuai kebutuhan anda semua para pembaca budiman.

Kasus 1: Multi Result 1 Kriteria

Dengan tabel kasus diatas kita ingin mengambil data "Item" sesuai dengan "Kode" 101, disana terlihat kode 101 punya beberapa item. Hasil yang kita inginkan nanti seperti ini...

Jadi hasil multi results bisa dibuat secara vertical ataupun horizontal.
Rumusnya:

Untuk Hasil Vertical:
=IFERROR(INDEX($B$2:$B$6;SMALL(IF(($A$2:$A$6=$B$9)*($B$2:$B$6<>"");ROW($B$2:$B$6)-ROW($B$2)+1);ROWS($B$1:B1)));"")
Kemudian copas ke bawah

Untuk Hasil Horizontal:
=IFERROR(INDEX($B$2:$B$6;SMALL(IF(($A$2:$A$6=$B$15)*($B$2:$B$6<>"");ROW($B$2:$B$6)-ROW($B$2)+1);COLUMNS($B$1:B1)));"")
Kemudian copas kesamping kanan

Kasus 2: Multi Results 2 Kriteria

Hampir sama, hanya ada penambahan kriteria yaitu Kolom "Group", hasil yang diinginkan...

Pada gambar diatas dengan 2 kriteria yaitu Kolom "Group" dan Kolom "Kode" dan hasil yang diambil adalah Kolom "Item", baik secara vertical maupun horizontal.
Yang kedua adalah 2 kriteria pada Kolom "Kode", ditampilkan secara vertical.

Rumus untuk yang vertical (di Cell C9 dan copas ke bawah):
=IFERROR(INDEX($C$2:$C$6;SMALL(IF(($B$2:$B$6=$B$9)*($A$2:$A$6=$B$10)*($C$2:$C$6<>"");ROW($C$2:$C$6)-ROW($C$2)+1);ROWS($C$1:C1)));"")

Rumus untuk hasil horizontal (di Cell C15 dan copas ke kanan):
=IFERROR(INDEX($C$2:$C$6;SMALL(IF(($B$2:$B$6=$B$15)*(A2:A6=B16)*($C$2:$C$6<>"");ROW($C$2:$C$6)-ROW($C$2)+1);COLUMNS($C$1:C1)));"")

Rumus untuk 2 Kriteria "101" dan "202" (sama2x kriteria Kode):
=IFERROR(INDEX($B$2:$C$6;SMALL(IF(COUNTIF($B$19:$B$20;INDEX($B$2:$C$6;;1;1))>0;ROW($B$2:$C$6)-MIN(ROW($B$2:$C$6))+1);ROW(A1));2);"")

Kasus 3: Multi Results pada 2 Tabel

Nah bagaimana dengan 2 Tabel? mari kita tampilkan hasil yang diinginkan....

Nah menurut gambar diatas, kita diminta menampilkan data "Item" pada 2 tabel sesuai 2 kriteria

Rumus untuk hasil vertical:
=IFERROR(IFERROR(INDEX($C$2:$C$6;SMALL(IF(($A$2:$A$6=$A$9)*($B$2:$B$6=$B$9);ROW($C$2:$C$6)-MIN(ROW($C$2))+1);ROWS($A$1:A1)));INDEX($G$2:$G$6;SMALL(IF(($E$2:$E$6=$A$9)*($F$2:$F$6=$B$9);ROW($G$2:$G$6)-MIN(ROW($G$2))+1);ROWS($A$1:A1)-COUNTIFS($A$2:$A$6;$A$9;$B$2:$B$6;$B$9))));"")

Dan untuk hasil horizontal:
=IFERROR(IFERROR(INDEX($C$2:$C$6;SMALL(IF(($A$2:$A$6=$A$9)*($B$2:$B$6=$B$9);ROW($C$2:$C$6)-MIN(ROW($C$2))+1);COLUMNS($A$1:A1)));INDEX($G$2:$G$6;SMALL(IF(($E$2:$E$6=$A$9)*($F$2:$F$6=$B$9);ROW($G$2:$G$6)-MIN(ROW($G$2))+1);COLUMNS($A$1:A1)-COUNTIFS($A$2:$A$6;$A$9;$B$2:$B$6;$B$9))));"")

Kasus 4: Multi Results dengan Menghilangkan Duplikasi

Kali ini coba perhatikan, misal kriterianya adalah "101", tetapi kok ada item yang sama ya? Nah kita inginnya membuang yang dobel itu. Hasil yang diinginkan..

Jadi item "Kemoceng" ditampilkan hanya satu saja... ini dia rumusnya
=IFERROR(INDEX($B$2:$B$6;SMALL(IF(($A$9=$A$2:$A$6)*(COUNTIF($B$8:B8;$B$2:$B$6)=0);ROW($A$2:$A$6)-MIN(ROW($A$2:$A$6))+1;"");1));"")
kemudian copas ke bawah

Oh iya semua rumus diatas adalah rumus array atau rumus CSE (CTRL+SHIFT+ENTER)

Semoga bermanfaat.... cheers

File Latihan