RumusHitung.com – Halo guys, bagaimana kabarnya? Semoga masih semangat yaa.. Kali ini rumushitung akan melanjutkan pembahasan mengenai rumus Excel. Pada pembahasan sebelumnya, sobat telah mempelajari tentang gabungan rumus Average pada Excel, pengelompokan data (grouping), dan gabungan rumus Rank pada Excel. Selanjutnya, kita akan bahas mengenai seri tanggal (date series) pada Excel.
Ada 6 contoh rumus seri tanggal (date series) pada Excel :
1) Seri tanggal menurut hari khusus
2) Seri tanggal berdasarkan hari
3) Seri tanggal berdasarkan bulan
4) Seri tanggal akhir pekan
5) Seri tanggal berdasarkan hari kerja
6) Seri tanggal berdasarkan tahun
Contents
Seri Tanggal Menurut Hari Khusus
Untuk menghasilkan rangkaian tanggal dinamis yang hanya mencakup hari-hari tertentu dalam seminggu (yaitu hanya hari Selasa dan Kamis), gunakan fungsi WORKDAY.INTL. Pada contoh yang ditampilkan, tanggal di B5 adalah tanggal mulai hardcode. Rumus di B4 adalah :
=WORKDAY.INTL(B3;1;”1010111″)
Rumus hanya menghasilkan hari Selasa dan Kamis ketika rumus disalin. Jika tanggal mulai di B3 diubah, tanggal baru dibuat secara dinamis.
Hari-hari khusus
Untuk menyesuaikan hari mana yang dihasilkan, cukup sesuaikan kodenya. Misalnya, untuk mengeluarkan hanya hari Senin, Selasa, atau Jumat, dapat menggunakan :
=WORKDAY.INTL(B3;1;”0101011″)
Seri Tanggal Berdasarkan Hari
Jika ingin membuat rangkaian tanggal dinamis dengan rumus yang bertambah satu hari dari satu tanggal mulai, lakukan dengan rumus yang sangat sederhana yang hanya menambahkan 1 ke setiap tanggal.
Pada contoh di atas, B3 adalah tanggal mulai hardcode dan rumus berada di sel B4 :
=B3+1
Karena tanggal di Excel hanyalah nomor seri (tanggal pertama dalam sistem tanggal standar Excel adalah 1 Januari 1900), sesuaikan tanggal hanya dengan menambahkan atau mengurangi nilai. Untuk menyelesaikan rumus ini, Excel hanya menambahkan 1 ke tanggal di B3. Oleh karena itu, rumus pertama menghasilkan tanggal baru 1/31/2000, satu hari lebih lambat dari tanggal mulai.
Setelah rumus pertama dimasukkan, rumus tersebut akan disalin sejauh yang diperlukan. Setiap rumus berikutnya membuat tanggal baru yang bertambah satu hari. Dapat dengan mudah menyesuaikan rumus yang menghasilkan tanggal mendatang menurut minggu, dengan menggunakan 7, bukan 1, seperti :
=B3+7
Seri Tanggal Berdasarkan Bulan
Untuk menghasilkan rangkaian tanggal dinamis dengan rumus yang bertambah satu bulan dari satu tanggal mulai, gunakan rumus berdasarkan fungsi DAY, MONTH, YEAR, dan DATE.
Pada contoh, B3 adalah tanggal mulai hardcode dan rumus di B4 adalah :
=DATE(YEAR(B3);MONTH(B3)+1;DAY(B3))
Untuk menyelesaikan rumus ini, Excel terlebih dahulu mengekstrak nilai tahun, bulan, dan hari dari tanggal di B3, lalu menambahkan 1 ke nilai bulan. Selanjutnya, tanggal baru disusun oleh fungsi DATE, menggunakan hari dan tahun yang sama, dan bulan + 1 untuk bulan.
=DATE(YEAR(B3);MONTH(B3)+1;DAY(B3))
=DATE(2010;1+1;15)
=DATE(2010;2;15)
=2/15/2010
Oleh karena itu rumus pertama menghasilkan tanggal baru 2/15/2010, satu bulan lebih lambat dari tanggal mulai.
Setelah rumus pertama dimasukkan, rumus tersebut akan disalin sejauh yang diperlukan. Setiap rumus berikutnya membuat tanggal baru yang bertambah satu hari.
Catatan: Jika dimulai dengan tanggal seperti 31 Januari, rumus di atas akan melewati Februari dan berlanjut ke Maret. Ini terjadi karena tanggal baru, 31/2/2010 tidak ada, sehingga Excel menggunakan nilai hari untuk bergulir maju ke 3/3/2010, 3 hari setelah hari terakhir di bulan Februari.
Jika dibutuhkan rangkaian tanggal di mana setiap tanggal adalah hari terakhir setiap bulan, gunakan EODATE seperti :
=EOMONTH(B3;1)
Seri Tanggal Akhir Pekan
Untuk menghasilkan rangkaian tanggal dinamis yang hanya akhir pekan (Sabtu dan Minggu), gunakan fungsi WORKDAY.INTL. Pada contoh yang ditampilkan, tanggal di B3 adalah tanggal mulai hardcode. Rumus di sel B4 :
=WORKDAY.INTL(B3;1;”1111100″)
Ini hanya menhasilkan hari Sabtu atau Minggu saat rumus disalin. Daftar ini dinamis – ketika tanggal mulai diubah, tanggal baru dibuat.
Cara lain
Jika memiliki versi Excel yang lebih lama tanpa fungsi WEEKDAY.INTL, gunakan rumus alternatif berdasarkan fungsi WEEKDAY :
=IF(WEEKDAY(B3)=7;B3+1;B3+(7-WEEKDAY(B3)))
Secara default, WEEKDAY akan mengembalikan 1 untuk Minggu dan 7 untuk Sabtu. Jika hasilnya 7, maka hasilnya B5 + 1 (Minggu). Jika tidak, hasilnya adalah :
=B3+(7-WEEKDAY(B3))
Yang menyelesaikan seperti ini, tergantung pada hari dalam seminggu :
B3+(7-2)=B3+5 <– Senin
B3+(7-2)=B3+4 <– Selasa
B3+(7-4)=B3+3 <– Rabu
B3+(7-5)=B3+2 <– Kamis
B3+(7-6)=B3+1 <– Jum’at
Catatan : berikan tanggal setidaknya satu hari sebelum Sabtu pertama yang ingin dibuat.
Seri Tanggal Berdasarkan Hari Kerja
Untuk membuat rangkaian tanggal dinamis yang hanya hari kerja (yaitu Senin sampai Jumat), gunakan fungsi WORKDAY. Pada contoh yang ditampilkan, rumus di sel B4 :
=WORKDAY(B3;1;E3:E4)
Cara lain
Dengan cara lain, dapat membuat rangkaian hari kerja dengan rumus yang menggunakan fungsi WEEKDAY. Dalam hal ini, rumus menguji tanggal mulai untuk melihat apakah itu hari Sabtu atau bukan. Jika demikian, 3 hari ditambahkan ke tanggal mulai. Jika tidak, hanya satu hari ditambahkan.
=IF(WEEKDAY(tanggal)=6;tanggal+3;tanggal+1)
Seri Tanggal Berdasarkan Tahun
Jika perlu membuat rangkaian tanggal dinamis dengan rumus yang bertambah satu tahun dari satu tanggal mulai, Anda dapat melakukannya dengan rumus yang menggunakan fungsi DAY, MONTH, YEAR, dan DATE.
Dalam contoh, B3 adalah tanggal mulai hardcode dan rumus di B4 :
=DATE(YEAR(B3)+1;MONTH(B3);DAY(B3))
Untuk menyelesaikan rumus ini, Excel terlebih dahulu mengekstrak nilai tahun, bulan, dan hari dari tanggal di B3, lalu menambahkan 1 ke nilai tahun. Selanjutnya, tanggal baru dipasang kembali oleh fungsi DATE, menggunakan hari dan bulan yang sama, dan tahun + 1 untuk tahun.
=DATE(YEAR(B3)+1;MONTH(B3);DAY(B3))
=DATE(2010+1;1;15)
=DATE(2011;1;15)
=1/15/2011
Oleh karena itu, rumus pertama menghasilkan tanggal baru 1/15/2011, satu tahun lebih lambat dari tanggal mulai.
Setelah rumus pertama dimasukkan, rumus tersebut akan disalin sejauh yang diperlukan. Setiap rumus berikutnya membuat tanggal baru yang bertambah satu hari.
Bisa dengan mudah menyesuaikan rumus ini jika diperlukan. Misalnya, jika membutuhkan rangkaian tanggal di mana setiap tanggal adalah hari pertama tahun baru, gunakan rumus seperti :
=DATE(YEAR(tanggal)+1;1;1)
Demikian penjelasan mengenai seri tanggal (date series) pada Excel, semoga bermanfaat. Sekian terima kasih.