26 April 2014

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