Salah satu kelompok fitur siap pakai di conditional formatting berhubungan tanggal. Kita bisa memanfaatkan fitur-fitur ini untuk menandai tanggal-tanggal berdasarkan kriteria tertentu.
Teman-teman kami persilakan untuk menggunakan file berikut di bawah sebagai bahan demo artikel ini.
File: 00-conditional-formatting-tanggal.xlsx.
Menggunakan Conditional Formatting untuk Tanggal dengan Preset
Menggunakan format siap pakai untuk menandai tanggal dengan conditional formatting sangat mudah.
Demo kali ini bertujuan untuk menandai tanggal-tanggal sampai satu bulan ke depan dengan aturan siap pakai.
(Demo 01) Berikut cara menggunakan conditional formatting untuk tanggal dengan format siap pakai.
- Pilih cell atau range di mana data tanggal akan ditandai, yaitu B3:F14.
- Klik Conditional Formatting → Highlight Cell Rules → A Date Occurring.
Gambar 01. Pengaturan siap pakai untuk di conditional formatting untuk tanggal.
- Pada Format cells that contain a date occurring, pilih Next Month.
- Pada with, pilih format yang diinginkan.
- Klik OK.
Bila Live Preview aktif, kita bisa lihat bagaimana conditional formatting diterapkan sebelum kita mengklik OK. Perhatikan Gambar 02.
Menggunakan Conditional Formatting untuk Tanggal dengan Custom Format
Selain format siap pakai, kita juga bisa menggunakan conditional formatting custom untuk menemukan tanggal tertentu dan menandainya.
Demo berikut untuk menemukan tanggal-tanggal sampai tujuh hari ke depan sejak tanggal hari ini.
(Demo 02) Berikut cara menggunakan conditional formatting untuk tanggal dengan aturan custom.
- Pilih cell atau range dengan data tanggal yang akan diperiksa, yaitu B3:F14.
- Klik Conditional Formatting → New Rule.
- Pada Select a Rule Type, pilih Format only cells that contain.
- Pada Format only cells with pilih Dates occurring, lalu pilih Next week.
- Klik Format akan membuka kotak dialog Format Cells. Dengan kotak dialog ini, Excel mengijinkan kita menyusun custom format untuk menandai tanggal yang diinginkan.
- Klik OK.
Hasil dari penerapan aturan dan format bisa dilihat pada gambar di bawah ini.
Secara prinsip, menggunakan aturan siap pakai dan custom tidak berbeda.
Keterbatasan Aturan Siap Pakai di Conditional Formatting
Perhatikan daftar aturan-aturan siap pakai.
Semua aturan di atas punya keterbatasan, yaitu mengacu pada tanggal hari ini. Interval terjauh adalah Next Month (bulan depan) dan Last Month (bulan lalu). Artinya, data yang bisa kita periksa sepanjang-panjangnya adalah adalah data dengan interval dua bulan (tanggal satu bulan ini sampai tanggal akhir bulan berikutnya).
Solusi untuk masalah ini adalah menggunakan rumus.
Cara Menggunakan Rumus Conditional Formatting untuk Tanggal di Excel
Menggunakan rumus bisa memecahkan banyak masalah dalam menggunakan conditional formatting untuk tanggal.
Keuntungan rumus conditional formatting adalah kita bisa menentukan sendiri batas-batas aturan tanggal sesuai kebutuhan.
Selain itu, rumus juga memungkinkan kita mencari data dengan conditional formatting berdasarkan cell lain.
Cara Mewarnai Tanggal di Excel dengan Rumus Conditional Formatting
Demo pada bagian ini bertujuan untuk mencari data berdasarkan tanggal tertentu. Pada kasus ini, kami akan menggunakan tanggal 2023-02-20.
Karena hanya mencari data dengan nilai tanggal tertentu, maka rumus yang akan digunakan adalah:
=B3=DATE(2023;02;20)
Catatan: Saat menulis rumus conditional formatting, kita harus selalu mulai dari cell terkiri atas dari keseluruhan range. Dalam konteks kelompok data yang kita gunakan, cell tersebut adalah B3.
(Demo 03) Berikut langkah-langkah menandai data berdasarkan tanggal tertentu:
- Pilih cell atau range dengan data tanggal, yaitu B3:F14.
- Klik Conditional Formatting → New Rule.
- Pada Select a Rule Type, pilih Use a formula to determine which cells to format.
- Pada Format values where this formula is true, masukkan rumus yang diinginkan, yaitu
=B3=DATE(2023;02;20). - Tentukan format yang dibutuhkan dengan mengklik tombol Format.
- Klik OK.
Hasilnya bisa kita lihat pada gambar di bawah ini.
Catatan: Kita bisa dengan mudah mengganti parameter DATE(2023;02;20) dengan tanggal hari ini atau tanggal apa pun yang kita inginkan.
Selain itu, kita juga bisa menggunakan operator pembanding lain seperti <>, <=, atau >=.
Cara Mewarnai Hari Minggu di Excel dengan Rumus Conditional Formatting
Demo berikut bertujuan untuk menandai setiap tanggal hari Sabtu dan Minggu.
Untuk mencapai tujuan ini, kami menggunakan fungsi WEEKDAY dengan sintaks sebagai berikut:
WEEKDAY(Tanggal)
Weekday akan mengembalikan angka 1 untuk hari Minggu dan 7 untuk hari Sabtu. Setiap tanggal terasosiasi dengan hari Sabtu ATAU Minggu. Dengan demikian, keseluruhan rumus yang akan kita gunakan adalah:
=OR(WEEKDAY(B3)=1; WEEKDAY(B3)=7)
(Demo 04) Berikut cara mewarnai hari Minggu di Excel.
- Pilih cell-cell dengan data tanggal, yaitu B3:F14.
- Klik Conditional Formatting → New Rule.
- Pada Select a Rule Type, pilih Use a formula to determine which cells to format.
- Pada Format values where this formula is true, masukkan rumus yang diinginkan, yaitu
=OR(WEEKDAY(B3)=1; WEEKDAY(B3)=7). - Tentukan format yang dibutuhkan dengan mengklik tombol Format.
- Klik OK.
Silahkan periksa gambar di bawah apakah conditional formatting bekerja menandai hari Minggu atau akhir pekan.
Tips: Bila mau Sabtu dan Minggu memiliki warna terpisah, teman-teman bisa pisahkan WEEKDAY(B3)=1 dan WEEKDAY(B3)=7 menjadi dua aturan.
Cara Mewarnai Hari Libur di Excel dengan Rumus Conditional Formatting
Demo kita kali ini adalah menandai tanggal-tanggal yang merupakan hari libur.
Excel, tentu saja, tidak tahu hari-hari libur. Karena itu kami menyiapkan data hari libur. Data utama nantinya akan mengambil referensi dari data ini.
Untuk mengevaluasi tanggal, kita memerlukan fungsi Excel, yaitu COUNTIF dengan sintaks sebagai berikut:
COUNTIF(Range; Nilai)
Nilai di sini adalah nilai dari data utama, sementara Range mengacu pada kelompok data hari libur.
COUNTIF akan mengembalikan nilai 0 bila tidak ada tanggal yang sesuai di data hari libur. Bila ada, COUNTIF akan mengembalikan nilai 1. Dengan demikian, keseluruhan format yang akan kita gunakan adalah:
=COUNTIF($H$3:$H$14;B3)=1
(Demo 05) Cara mewarnai hari libur di Excel.
- Pilih lokasi worksheet dengan data tanggal yang ingin ditandai, yaitu B3:F14.
- Klik Conditional Formatting → New Rule.
- Pada Select a Rule Type, pilih Use a formula to determine which cells to format.
- Pada Format values where this formula is true, masukkan rumus yang diinginkan, yaitu
=COUNTIF($H$3:$H$14;B3)=1. - Tentukan format yang dibutuhkan dengan mengklik tombol Format.
- Klik OK.
Hasilnya bisa kita lihat pada gambar di bawah ini.
Penutup
Demikian bahasan kami kali ini.
Seperti teman-teman lihat pada demo-demo di atas, kita bisa menggunakan aturan siap pakai dan rumus dalam menggunakan conditional formatting untuk tanggal.
Rumus secara khusus mampu memperluas fungsi conditional formatting di luar batas aturan siap pakai.
Hasil dari demo-demo di atas bisa didownload pada file di bawah ini.