Deal with Dates Before 1900 (Menghitung Umur Sebelum Tahun 1900)

Ass.Wr.Wb.

Salam semuanya, tulisan saya kali ini masih berkaitan dengan tanggal, mudah2xan tidak membuat bosan :) , belum lama ini saya mencoba untuk mencari cara untuk membuat rumus menghitung umur di postingan saya di "menghitung umur tanpa fungsi datedif", bisa bekerja untuk kalkulasi tahun sebelum 1900. Entah kenapa Microsoft tidak mendukungnya di MS Excel, pasti error jika menggunakan rumus. Saya coba cek tanggal yang seperti ini misal 8/5/1456 , saya test menggunakan =ISNUMBER

dan hasilnya:

Ya hasilnya FALSE, berarti MS Excel tidak mengenali tanggal tersebut karena tahunnya sebelum 1900, dengan kata lain anda mengetikkan tanggal "1456" maka tanggal tersebut terformat sebagai text bukan angka. Saya coba eksperimen selalu gagal akhirnya ketemu, yaitu dengan menambahkan angka tahun dengan jumlah angka tertentu supaya ter-konversi menjadi angka. Saya coba angka2x kecil +200, +300, +400, tetapi belum benar hasilnya. Akhirnya ketemu angka 7000, saya coba tambahkan angka diatas itu sudah tidak bisa alias error. Nah kita buka rumusnya dengan 2 kasus. Saya memakai contoh menghitung umur dari tanggal lahir (start date) dan meninggal (end date), anda bisa menggunakan istilah lain, mungkin end date bisa diganti dengan =TODAY untuk menghitung umur sampai hari ini

Kasus 1

Mungkin tidak semua orang butuh ya, kenapa harus menghitung umur pada abad 1? Ya untuk membuktikan rumus berhasil. Dan rumus ini juga bisa digunakan untuk tahun setelah 1900.
Rumus:
=DATEDIF(DATE(B2+7000;C2;D2);DATE(B3+7000;C3;D3);"y")&" Tahun"&" "&IF(DATEDIF(DATE(B2+7000;C2;D2);DATE(B3+7000;C3;D3);"MD")>=0;DATEDIF(DATE(B2+7000;C2;D2);DATE(B3+7000;C3;D3);"YM");DATEDIF(DATE(B2+7000;C2;D2);DATE(B3+7000;C3;D3);"YM")-1)&" Bulan"&" "&IF(DATEDIF(DATE(B2+7000;C2;D2);DATE(B3+7000;C3;D3);"MD")>=0;DATEDIF(DATE(B2+7000;C2;D2);DATE(B3+7000;C3;D3);"MD");DATEDIF(DATE(B2+7000;C2;D2);DATE(B3+7000;C3;D3);"MD")+31)&" Hari"

Kasus 2

Untuk kasus ke-2 ini rumusnya sangat puanjaang...... sehingga tidak saya tulis disini, tetapi saya sediakan di file exercise, rumusnya mudah2xan akurat, bisa digunakan untuk semua tahun ya, baik sebelum maupun setelah tahun 1900.

FILE LATIHAN

Merubah Desimal ke Tahun dan Bulan

Selamat pagi readers...bahagia bisa posting lagi, kali ini tentang desimal untuk di konversi ke "Tahun" dan "Bulan". Misal seperti ini di cell A1=1, maka Excel menerjemahkannya sebagai 1 Tahun, kemudian 1,5 diterjemahkan menjadi 1 Tahun 6 Bulan.

Langkahnya:
A. TAHUN
Anda bisa menggunakan fungsi INT atau TRUNC untuk mengambil tahun, ke-2 fungsi ini mengambil angka di depan koma pada desimal. Kasusnya dan langkah penyelesaiannya silahkan diikuti dibawah ini:

Asumsi di cell A1 ada angka 1,5.
a.=INT(A1) atau =TRUNC(A1;0)
b. Tambahkan dengan teks "Tahun", menjadi =INT(A1)&" Tahun" atau =TRUNC(A1;0)&" Tahun ", ada spasi ya diantara kata "Tahun".
c. Untuk menghindari muncul "0 Tahun" jika kriteria di A1 dibawah 1, misal 0,5, maka "0 Tahun" ditampilkan sebagai blank atau teks kosong, secara rumus: =IF(INT(A1);INT(A1)&" Tahun";"") atau =IF(TRUNC(A1;0);TRUNC(A1;0)&" Tahun ";"")

B. BULAN
 Nah untuk bulan, angka yang diambil dari desimal adalah angka yang dibelakang koma,
a. Rumusnya =INT((A1-INT(A1))*12) atau =TRUNC((A1-INT(A1))*12), hasilnya "6", artinya 6 Bulan, silahkan ditambahkan saja dengan teks "Bulan" dibelakangnya.
b. Ditambahkan kondisi menampilkan teks kosong atau blank jika krierianya bukan desimal, misal angka 1,2 dst. Rumusnya menjadi =IF((A1-INT(A1))*12;INT((A1-INT(A1))*12)&" bulan ";"") atau =IF((A1-INT(A1))*12;TRUNC((A1-INT(A1))*12)&" bulan";"")

Nah semuanya ketemu baik "Tahun" dan "Bulan", finalisasi rumus menjadi:
1. =IF(INT(A1);INT(A1)&" tahun ";"")&IF((A1-INT(A1))*12;INT((A1-INT(A1))*12)&" bulan ";"")
2. =IF(TRUNC(A1;0);TRUNC(A1;0)&" tahun ";"")&IF((A1-INT(A1))*12;TRUNC((A1-INT(A1))*12)&" bulan";"")

Sekian dari saya
Salam semuanya
Rizky

Mengetahui Zodiak, Weton Dan Shio Dari Tanggal

Ass.Wr.Wb.

Salam semuanya dan senang menjumpai anda semuanya kali ini, tulisan saya sekarang ini sebetulnya perbaikan postingan saya yang berjudul "Kumpulan Trik Rumus untuk Tanggal", jadi saya hapus dan digantingan dengan perbaikan serta tambahan di tulisan ini.

Postingan membahas tentang mengetahui zodiak/bintang, weton dan shio dari tanggal lahir anda, hanya sebatas untuk pengetahuan saja, tidak usah mempercayai ramalannya ya...
Tidak lupa juga rumus untuk konversi tanggal Masehi ke tanggal Hijriyah.

1. ZODIAK

Hasil yang diinginkan

Rumus yang menggunakan tabel data zodiak ada 2:
1. =IF(E2="";"-";INDEX($A$4:$A$15;MATCH(1;IF((DATE(2000;MONTH(E2);DAY(E2))>=  DATE(2000;MONTH($B$4:$B$15);DAY($B$4:$B$15)));1;0);1)))
2. =IF(E2="";"-";INDEX(A$3:A$15;MATCH(1;IF(--TEXT(E2;"dmmm")>=--TEXT(B$3:B$15; "dmmm");IF(--TEXT(E2;"dmmm")<=--TEXT(C$3:C$13;"dmmm");1));1)))

Semuanya formula array, jadi jangan lupa tekan tombol CTRL+SHIFT+ENTER bersamaan.

Rumus tanpa menggunakan tabel ada 2 juga:
1. =IF(E7="";"-";LOOKUP(--TEXT(E7;"mdd");{101;121;220;321;421;521;622;723;824;923;1023;1123;1222};{"Capricorn";"Aquarius";"Pisces";"Aries";"Taurus";"Gemini";"Cancer";"Leo";"Virgo";"Libra" ;"Scorpio";"Sagittarius";"Capricorn"}))
2. =IF(E7="";"-";INDEX({"Capricorn";"Aquarius";"Pisces";"Aries";"Taurus";"Gemini";"Cancer";"Leo" ;"Virgo";"Libra";"Scorpio";"Sagittarius";"Capricorn"};MATCH(MONTH(E7)*100+DAY(E7);{0;121 ;220;321;421;522;622;723;823;924;1024;1123;1222})))
Wah panjangnya.....

2. WETON

Weton adalah perhitungan jawa untuk pernikahan atau nasib berdasar hari kelahirannya dan merupakan peninggalan budaya jawa.

Rumus menggunakan tabel ada 2:
1. =VLOOKUP(MOD(E2;7);A3:B9;2) &" " &VLOOKUP(MOD(E2;5);A3:C9;3)
 2. =INDEX(B3:B9;MATCH(MOD(E2;7);A3:A9;0))&" "&INDEX(C3:C7;MATCH(MOD(E2;5); A3:A7;0))

Rumus tanpa tabel lookup ada 2:
1. =TEXT(E7;"dddd")&" "&CHOOSE(MOD(E7;5)+1;"Kliwon";"Legi";"Pahing";"Pon";"Wage")
2. =TEXT(WEEKDAY(E7);"dddd")&" "&CHOOSE({1\2\3\4\5};INDEX({"Kliwon";"Legi";"Pahing" ;"Pon";"Wage"};MOD(E7;5)+1))

3. SHIO

Rumus menggunakan tabel lookup:
1. Shio =IF(D10="";"-";VLOOKUP(MOD((YEAR(D10)-4);12);A3:B14;2))
2. Elemen =IF(D10="";"-";VLOOKUP(INT(MOD((YEAR(D10)-4);10)/2);D3:E7;2))

Rumus tanpa tabel lookup:
1. Shio
=IF(D13="";"";INDEX({"Tikus";"Kerbau";"Macan";"Kelinci";"Naga";"Ular";"Kuda";"Kambing"; "Monyet";"Ayam";"Anjing";"Babi"};MATCH(MOD((YEAR(D13)-4);12);({0;1;2;3;4;5;6;7;8;9;10;11}) ;0)))
2. Elemen  
=IF(D13="";"";LOOKUP(INT(MOD((YEAR(D13)-4);10)/2);{0;1;2;3;4;5;6;7;8;9;10;11};{"Kayu";"Api" ;"Tanah";"Logam";"Air"}))

4. KONVERSI TANGGAL MASEHI KE HIJRIYAH

Sebetulnya pada Excel sudah dibekali kode untuk konversi tanggal masehi atau gregorian date ke hijriyah yaitu "b2" yang digabungkan dengan Fungsi TEXT. Tetapi tidak ada salahnya saya memberikan contohkan dengan menggunakan tabel.

Rumus dengan tabel lookup:
=TEXT(C2-1;"b2d")&" "&INDEX(A2:A13;TEXT(C2-1;"b2m"))&" "&TEXT(C2-1;"b2yyyy")

Rumus tanpa tabel, ada 2:
1. =TEXT(C5-1;"b2d ")&INDEX({"Muharram";"Shafar";"Rabi'ul Awwal";"Rabi’ul Akhir";"Jumadil Awwal";"Jumadil Akhir";"Rajab";"Sya'ban";"Ramadhan";"Syawwal";"Dzulqa'dah";"Dzulhijjah"} ;TEXT(C5-1;"b2m"))&" "&TEXT(C5-1;"b2yyyy")
2. =TEXT(C5-1;"b2mmm d"&" ,yyy")

Sudah capek? beluuum dong...... pengennya nambahin lagi, ada tambahan trik rumus untuk tanggal, tetapi tidak saya tulis disini tetapi di FILE LATIHAN, silahkan di download dan di pelajari. Mudah2xan ada manfaatnya.

Wass.Wr.Wb.

Download File Latihan