Pada artikel ini kita akan membahas cara menghitung usia dalam Excel. Usia yang dimaksud di sini tidak hanya usia manusia, tetapi bisa juga pada makhluk lain atau pada benda.
Dalam Excel kita bisa menghitung umur dengan menggunakan beberapa cara. Cara menghitung umur ini bisa bergantung pada formula yang Anda gunakan.
Hal yang perlu diperhatikan dalam menghitung umur di Excel adalah satuan yang ingin Anda gunakan.
Anda bisa menghitung usia dalam satuan tahun, bulan atau hari.
Menghitung Umur dalam Satuan Tahun
Tahun merupakan satuan yang sering digunakan ketika kita ingin menghitung umur seseorang.
Untuk menghitung umur dalam tahun, Anda bisa menggunakan formula berikut ini:
=(end_date–start_date)/365
Di mana:
- start_date: Birth date
- end_date: Today Date
Kelemahan dari formula di atas adalah tidak semua tahun memiliki hari yang sama yaitu 365 hari. Selain 365 ada juga tahun yang memiliki jumlah hari sebanyak 366 hari. Tahun ini dinamakan tahun kabisat, dimana bulan Februari terdiri dari 29 hari.
Tahun-tahun kabisat atau yang memiliki jumlah hari sebanyak 366 adalah tahun yang jika dibagi 4 nilainya tidak bersisa. Misal 2016 habis dibagi 4, 2020 habis dibagi 4.
Sebagai alternatif membagi dengan 365 Anda bisa menggunakan pembagian dengan 365.25. Dasar penambahan 0.25 (1/4) karena tahun kabisat terjadi 4 tahun sekali.
Cara ini juga memiliki kelemahan karena tidak terlalu akurat digunakan untuk seorang bayi yang usianya belum mencapai 4 tahun atau bisa meleset beberapa bulan atau hari dalam bentuk desimal untuk perhitungan pada waktu-waktu tertentu.
Menghitung Umur dalam Tahun dengan Menggunakan Fungsi YEARFRAC
Cara yang lebih akurat untuk menghitung umur dalam tahun adalah dengan menggunakan Fungsi YEARFRAC.
Fungsi YEARFRAC memiliki bentuk perumusan sebagai berikut:
YEARFRAC(start_date, end_date, [basis])
Penjelasan argument dalam Fungsi di atas adalah sebagai berikut:
start_date : Merupakan tanggal start atau bisa diisi dengan tanggal lahir.
end_date : Merupakan tanggal akhir atau bisa diisi dengan tanggal yang ingin Anda hitung perbedaannya dengan tanggal lahir (awal).
basis : Merupakan argument yang nilainya terdiri dari 0 sampai 4, di mana:
Jika basis tidak diisi, maka nilai defaultnya adalah 0.
Contoh penggunaan rumus YEARFRAC:
Pada contoh di atas TODAY() diambil pada tanggal 11 Dec 2020. Angka decimal pada contoh di atas menunjukkan bahwa ada sisa umur dalam hari.
Jika Anda hanya memerlukan angka dalam tahun, Anda dapat menghilangkan desimal dengan menggunakan fungsi ROUNDOWN. Gunakan argument 0 untuk menghilangkan decimal.
Rumus yang digunakan untuk menghitung umur dalam tahun di Excel menjadi:
= ROUNDDOWN(YEARFRAC(C2,TODAY(),1),0)
Baca juga tentang fungsi IF di excel
Menghitung Umur di Excel dengan Fungsi DATEDIF
Cara lain untuk menghitung umur di Excel adalah dengan menggunakan fungsi DATEDIF.
Untuk menggunakan fungsi ini, gunakan rumus:
DATEDIF(start_date, end_date, unit)
Dengan Fungsi ini Anda dapat menampilkan data dalam bentuk yang lebih lengkap yaitu bisa dalam Tahun, bulan atau hari sesuai dengan unit yang Anda gunakan pada formula.
Untuk melihat umur dalam tahun, Anda dapat menggunakan unit Y sebagai berikut:
DATEDIF(start_date, end_date, “y”)
Contoh:
Seperti hasil pada contoh sebelumnya jumlah tahun yang didapat sama.
Cara Mendapatkan Umur dalam Tahun Bulan dan Hari
Selain dalam tahun, Anda juga dapat melakukan perhitungan umur yang lebih detail dengan membuat rumus menghitung data dalam Tahun, bulan dan hari.
Untuk menghitungnya, gunakan rumus berikut:
- Menghitung jumlah umur dalam tahun: =DATEDIF(start_date, end_date, “Y”)
- Menghitung jumlah bulan setelah dikurangi tahun: =DATEDIF(start_date, end_date, “YM”)
- Menghitung jumlah hari setelah dikurangi tahun dan bulan: =DATEDIF(start_date, end_date,”MD”)
Rumus di atas dapat ditulis dalam 3 sel yang berbeda di Excel atau bisa juga digabung dengan menggunakan Fungsi CONCATENATE atau operator &.
Jika ditulis pada 3 sel Excel yang berbeda maka hasilnya sebagai berikut:
Anda juga bisa menggabungkan teks penulisan tahun, bulan dan hari di atas seperti berikut:
=DATEDIF(start_date, end_date,”Y”) & “ Years, ” & DATEDIF(start_date, end_date,”YM”) & “ Month, “ & DATEDIF(start_date, end_date,”MD”) & “ Days.”
Rumus di atas jika diterapkan maka hasilnya sebagai berikut:
Anda bisa menyembunyikan Years, Month dan Days jika ternyata nilai masing-masing nol. Gunakan rumus berikut ini:
=IF(DATEDIF(start_date, end_date,”y”)=0,””,DATEDIF(start_date, end_date,”y”)&” years, “)& IF(DATEDIF(start_date, end_date,”ym”)=0,””,DATEDIF(start_date, end_date,”ym”)&” months, “)& IF(DATEDIF(start_date, end_date,”md”)=0,””,DATEDIF(start_date, end_date,”md”)&” days”)
Contoh penggunaan rumus seperti di bawah ini:
Jika dilihat rumus di atas masih memiliki kelemahan, yaitu ketika bulan dan hari bernilai nol tanda koma tidak hilang di belakang Years (liat baris ke tujuh).
Anda bisa mencoba membuat penulisan rumus yang lebih baik dengan menggunakan fungsi IF yang lebih detail. Silahkan tulis di kolom komentar jika berkenan.
Note: Anda bisa mencari usia atau jarak antara dua tanggal dengan mengganti argument end_date pada contoh di atas dengan tanggal lainnya (selain TODAY()). Agar lebih fleksibel end_date juga bisa Anda referensikan pada sel tertentu di Excel.
Menentukan Tanggal Usia ke-N Seseorang
Misalkan Anda memiliki informasi tanggal kelahiran seseorang. Kemudian Anda ingin mencari tahu pada tanggal berapa orang tersebut berusia 40 tahun?
Anda bisa menggunakan fungsi berikut:
DATE(year,month,day)
Penjelasan Fungsi di atas sebagai berikut (diambil dari situs Microsoft):
Jika Anda memiliki informasi tanggal lahir seseorang misalkan 30 Juli 1984. Kapan orang tersebut berumur 40 tahun?
Pada rumus, Anda masukkan data menjadi seperti berikut ini:
=DATE(YEAR(C2) + 40, MONTH(C2), DAY(C2))
Atau Anda juga bisa merumuskan sebagai berikut:
Pada contoh di atas angka 40 direferensikan ke sel F2.
Menghitung Umur dari Tanggal Lahir yang dipisah Tahun, bulan dan tanggalnya
Bagaimana jika data tanggal dipisah dalam 3 kolom yang berbeda, misal dalam tahun, bulan dan hari?
Anda bisa gabungkan terlebih dulu data tahun, bulan dan hari dengan fungsi DATE.
Setelah itu Anda hitung umurnya seperti pada contoh di bawah pada kolom Age, hasilnya sebagai berikut:
Atau Anda bisa langsung masukkan rumusnya seperti cara berikut:
=DATEDIF(DATE(C2, MONTH(DATEVALUE(D2&”1″)), E2), TODAY(), “y”) & ” Years, “& DATEDIF(DATE(C2, MONTH(DATEVALUE(D2&”1”)), E2),TODAY(), “ym”) & ” Months, “& DATEDIF(DATE(C2, MONTH(DATEVALUE(D2&”1”)), E2), TODAY(), “md”) & ” Days”
Membuat Calculator Usia di Excel
Untuk membuat kalkulator umur di Excel Anda perlu menggunakan kembali rumus-rumus yang sudah dibahas di atas.
Sebelum melakukan penghitungan umur ada beberapa data yang perlu diisi terlebih dulu, yaitu:
- Data tanggal lahir
- Data tanggal akhir (tanggal di mana umur dihitung) . Tanggal akhir bisa dipilih tanggal hari ini atau tanggal tertentu yang Anda input.
Berikut ini merupakan contoh form kalkulasi umur.
Pada form pengisian umur di atas proses yang dilakukan pertamakali adalah:
- User mengisi kolom tanggal lahir
- Ada validasi bahwa tanggal lahir yang diisi tidak boleh lebih besar dari tanggal hari ini.
- User bisa memilih apakah mau menghitung umur dari tanggal lahir sampai tanggal hari ini atau sampai tanggal tertentu yang diisikan pada excel.
- Ada validasi tanggal spesifik yang diisikan harus lebih besar dari tanggal lahir.
Setelah kita tahu proses yang diinginkan, maka pertamakali sediakan kolom seperti yang sudah dibahas, yaitu:
Kolom Fill Birth Date: Buat validasi pada kolom ini agar tanggal lahir tidak melebihi tanggal hari ini.
Kolom checklist option : Age as of Today dan Specific Date
Masukkan Option Button dengan mengakses tab Developer >> Insert >> pilih Option Button.
Arahkan mouse ke sel B11 dan B12, kemudian klik kiri untuk membuat option button.
Dalam keadaan Option Button masih terpilih, akses menu Developer >> Properties >> Masuk ke tab Control.
Pada kolom Cell link klik button dan arahkan link Anda pada salah satu sel di Excel. Pada contoh saya arahkan ke sel F2.
Link ke F2 untuk menempatkan nilai dari tombol option yang diklik. Misal jika user mengklik option Age as of Today maka nilai di F2 menjadi 1 atau mengklik Specific Date maka nilai di F2 menjadi 2.
Pilihan Specific Date,memungkinkan user untuk mengisi tanggal pada sel C12.
Pada sel C12 juga terdapat validasi data agar user tidak menginput tanggal yang lebih kecil dari tanggal lahir.
Pada bagian perhitungan umur, terdapat 4 kolom untuk menampilkan data umur dalam format yang berbeda, yaitu:
Kolom Age (y,m,d) untuk menampilkan umur dalam tahun, bulan dan hari. Rumus pada kolom ini (C14):
=IF($F$2=1,IF(DATEDIF(C9, C11,”y”)=0,””,DATEDIF(C9, C11,”y”)&” year(s) “)& IF(DATEDIF(C9, C11,”ym”)=0,””,DATEDIF(C9, C11,”ym”)&” month(s) “)& IF(DATEDIF(C9, C11,”md”)=0,””,DATEDIF(C9, C11,”md”)&” day(s)”),IF(ISNUMBER(C12),IF(DATEDIF(C9, C12,”y”)=0,””,DATEDIF(C9, C12,”y”)&” year(s) “)& IF(DATEDIF(C9, C12,”ym”)=0,””,DATEDIF(C9, C12,”ym”)&” month(s) “)& IF(DATEDIF(C9, C12,”md”)=0,””,DATEDIF(C9, C12,”md”)&” day(s)”),””))
Kolom Age in Year, untuk menampilkan umur total dalam tahun. Rumus pada kolom ini (C15):
=IF($F$2=1,IF(DATEDIF(C9, C11,”y”)=0,””,ROUND(YEARFRAC(C2,TODAY(),1),2)&” year(s)”),IF(ISNUMBER(C12),IF(DATEDIF(C9, C12,”y”)=0,””,ROUND(YEARFRAC(C7,C12,1),2)&” year(s)”),””))
Kolom Age in Month, untuk menampilkan umur total dalam bulan. Rumus pada kolom ini:
=IF($F$2=1,DATEDIF($C$9,TODAY(),”m”)&” month(s)”, IF(ISNUMBER($C$12),DATEDIF($C$9, $C$12,”m”)&” month(s)”, “”))
Kolom Age in Day, untuk menampilkan umur total dalam hari. Rumus pada kolom ini:
=IF($F$2=1,DATEDIF($C$9,TODAY(),”d”)&” day(s)”, IF(ISNUMBER($C$12),DATEDIF($C$9, $C$12,”d”)&” day(s)”, “”)).
Anda bisa implementasikan langsung rumus di atas untuk membuat calculasi umur di Excel, seperti pada contoh.
Cara Highlight Umur Dengan Menggunakan batas Atas dan Batas Bawah Umur Tertentu
Biasanya ketika kita mengolah data yang berkaitan dengan umur, Ada saatnya kita perlu menghighlight umur tertentu apakah melewati atau kurang dari batas yang ditetapkan.
Anda bisa menggunakan Conditional Formatting untuk meng-highlight batas umur tertentu pada Excel, caranya sebagai berikut:
- Masuk ke menu Home >> Conditional Formatting >> Manage Rules..
- Pada window Conditional Formatting Rules Manager click New Rule..
- Pada window New Formatting Rule masukkan rumus =YEARFRAC($J6,TODAY(),1)<$N$5 (seperti pada gambar).
- Setelah itu klik tombol Format, untuk memilih format yang diinginkan.
- Pada window Format Cells, pilih warna orange, lalu klik OK. Klik OK sekali lagi.
- Buat lagi Rule dengan mengklik New Rules.. sekali lagi.
- Dengan cara yang sama seperti sebelumnya masukkan rumus =YEARFRAC($J6,TODAY(),1)>=40 (40 diganti N5). Pilih format warna hijau untuk highlight. Pastikan format Anda sudah sesuai seperti pada gambar.
- Isi kolom Applies to sesuai range table yang Anda buat di Excel Anda. Pada contoh range table berada di area =$I$6:$K$13.
- Lanjutkan dengan klik OK dan klik tombol Apply.
- Contoh hasilnya seperti table di bawah:
Terlihat dari contoh di atas bahwa untuk umur yang nilainya di bawah 40 tahun highlight berwarna orange. Sedangkan untuk umur yang lebih atau sama dengan 40 tahun highlight berwarna hijau.
Untuk template perhitungan usia di Excel seperti contoh di atas, dapat Anda download di sini.
Baca Juga:
- Fungsi TIME
- Fungsi TIMEVALUE
- fungsi WEEKDAY
- fungsi WEEKNUM
- fungsi DATE
- fungsi DATEVALUE
- fungsi DAYS
- fungsi DAYS360
- fungsi EDATE
- fungsi EOMONTH
- fungsi ISOWEEKNUM
- fungsi NETWORKDAYS, NETWORKDAYS.INTL
- fungsi WORKDAY, WORKDAY.INTL
- fungsi YEAR, MONTH, DAY
- fungsi YEARFRAC
- Menghitung selisih waktu dalam jam dan menit di Excel
Penulis berkomitmen untuk terus berbagi pengetahuannya dengan komunitas melalui blog tutorialnya.
Tujuannya adalah untuk membantu pembaca meningkatkan efisiensi, produktivitas, dan keberhasilan dalam proyek-proyek dengan memanfaatkan alat-alat perangkat lunak yang telah terbukti efektif.
Dengan cara ini, Penulis berharap dapat memberikan kontribusi positif bagi perkembangan karir dan kesuksesan profesional pembacanya di bidang manajemen proyek.
- Mengecek Salah Ketik (Typo) Kata Bahasa Inggris di Word – April 26, 2024
- Menerjemahkan Sebagian atau Seluruh Isi Dokumen Word – April 25, 2024
- Mengecek Dokumen Word Sebelum Berbagi Dokumen – April 25, 2024
