Pemformatan Bersyarat di Excel dapat dianggap sebagai utilitas berharga yang dapat membantu menyempurnakan analisis dan presentasi data secara visual dalam spreadsheet dengan menerapkan pemformatan tertentu berdasarkan aturan atau ketentuan yang telah ditentukan sebelumnya. Posting ini akan membagikan bagaimana Anda dapat mengatur pemformatan bersyarat di Excel untuk tanggal. Kami juga menyertakan contoh detail yang dapat Anda coba sambil membaca panduan ini.
nonaktifkan cache memori
Cara mengatur Pemformatan Bersyarat di Excel untuk Tanggal
Pemformatan bersyarat tidak hanya membantu menyorot sel menggunakan kode warna tetapi juga memformat baris, kolom, dan sel yang berisi tanggal dengan cara yang dianggap bermakna dan menarik secara visual.
- Telah ditentukan sebelumnya
- Ditetapkan pengguna
- Buat aturan pemformatan yang dibuat khusus
- Menyoroti Hari Libur di Excel
- Pemformatan Tanggal Bersyarat berdasarkan beberapa kondisi
- Menyoroti Tanggal dan Penundaan yang akan datang
Pastikan untuk membaca detail di Excel dengan cermat. Panduan ini menawarkan banyak opsi, beberapa di antaranya mungkin berada di luar cakupan panduan ini.
1] Aturan tanggal pemformatan bersyarat yang telah ditentukan sebelumnya dengan Tanggal
Pemformatan bersyarat dengan tanggal dapat bermanfaat saat menangani kumpulan data yang menyertakan informasi terkait tanggal. Excel menyediakan hampir sepuluh opsi berbeda untuk memformat sel berdasarkan tanggal, seperti yang diilustrasikan di bawah ini:
- Untuk memformat sel dengan tanggal sebagai Tipe Data, Klik Beranda > Pemformatan Bersyarat > Sorot Aturan Sel >Tanggal Terjadi
- Opsi pemformatan bersyarat dapat dipilih dari menu drop-down di jendela berikutnya. Drop-down pertama memungkinkan kita memilih rentang hari, dan opsi pemformatan dapat dipilih dari yang kedua.
- Klik OKE untuk menerapkan aturan pemformatan yang dipilih.
2] Aturan tanggal pemformatan bersyarat yang ditentukan pengguna dengan Tanggal
Selain seperangkat aturan yang telah ditentukan sebelumnya, Excel juga menyediakan opsi untuk menyesuaikan aturan ini berdasarkan kebutuhan pengguna. Ada beberapa cara untuk mengatur penyesuaian, seperti yang diilustrasikan di bawah ini:
Menggunakan Format Kustom
- Buka Suatu Tanggal Terjadi format jendela dengan mengikuti langkah-langkah yang disebutkan di atas.
- Setelah memilih rentang hari, pilih Format Kustom dari menu tarik-turun opsi pemformatan.
- Itu Jendela Format Sel akan muncul karena hal di atas, dimana kita dapat memilih Warna, Perbatasan, Font, dan Efek pilihan kita lalu klik OK untuk menerapkan perubahan.
Buat Aturan Baru
Metode ini melibatkan pembuatan aturan yang sepenuhnya baru untuk pemformatan bersyarat, tidak seperti metode di atas, di mana kita dapat memilih dari beberapa aturan yang telah ditentukan sebelumnya dan hanya membuat format khusus. Membuat aturan baru melibatkan:
- Memilih sel yang pemformatannya akan diterapkan.
- Di Rumah Tab, klik Pemformatan Bersyarat > Aturan Baru , yang seharusnya membuka Jendela Aturan Pemformatan Baru.
- Di jendela tersebut, kita dapat memilih dari yang tersedia Jenis Aturan , diikuti dengan mengedit deskripsi aturan untuk menyiapkan kriteria pemformatan bersyarat.
- Setelah selesai, itu Format tombol dapat diklik di sudut kanan bawah jendela untuk memilih format aturan tersebut.
- Di Format Sel jendela yang terbuka, kita dapat memilih Font, Border, Cell Color, Effects, dll., sesuai pilihan dan klik OK sekali pada jendela ini, diikuti oleh Aturan Pemformatan Baru Jendela untuk menerapkan perubahan.
Catatan: Aturan pemformatan dapat digunakan untuk membuat dan menerapkan lebih dari satu aturan untuk kumpulan data yang sama berdasarkan kondisi/kriteria yang berbeda.
Membaca: Filter Tanggal Excel tidak dikelompokkan berdasarkan bulan
3] Cara membuat aturan pemformatan khusus dengan tanggal
Dalam contoh di atas, kita menavigasi langkah-langkah untuk membuat Aturan Baru untuk Pemformatan Bersyarat berdasarkan kriteria yang sudah disediakan. Excel juga memungkinkan penerapan aturan pemformatan tersebut pada kriteria yang ditentukan pengguna.
Misalnya, kita perlu menyorot sel dengan tanggal yang lebih tua dari 30 hari atau 60 hari. Dalam hal ini, hal yang sama juga dapat diterapkan dengan menerapkan rumus tanggal dan fungsi yang tersedia di Excel, seperti yang diilustrasikan di bawah ini:
mengkonversi mov ke mp4 windows 10
A] Sorot Tanggal Saat Ini dan Tanggal Lama
Di bawah Pilih Aturan Baru di opsi Pemformatan Bersyarat, pilih Gunakan Rumus untuk menentukan sel mana yang akan diformat , jenis:
=$H4=HARI INI() untuk menyoroti hari ini
=$H4>HARI INI() untuk menyoroti hari-hari mendatang
=$H4
=$H4<=(HARI INI()-30) untuk menyorot tanggal yang lebih dari 30 hari.
=$H4<=(HARI INI()-60) untuk menyorot tanggal yang berumur lebih dari 60 hari.
Rumus di atas memeriksa rentang tanggal yang dipilih untuk tanggal saat ini, dan setelah kecocokan ditemukan, ubah warna latar belakang menjadi Ungu seperti yang dipilih dari opsi Format. Itu $ digunakan di samping H menandakan bahwa kolom akan tetap konstan, tetapi baris akan bervariasi.
- Sekali Deskripsi Aturan disebutkan, Font, Warna, dan Efek dapat dipilih dengan mengklik tombol Format.
- Klik OKE pada jendela Format Cells, diikuti dengan hal yang sama pada jendela New Rule, agar perubahannya efektif.
B] Sorot berdasarkan rentang tanggal yang ditentukan pengguna
Metode serupa dapat diterapkan untuk memformat rentang tanggal yang ditentukan oleh pengguna. Misalnya, jika kita perlu menyorot tanggal yang lebih dari 30 hari tetapi kurang dari 45 hari, fungsi TODAY() dapat digunakan bersama dengan operator logika seperti AND untuk menentukan kriteria yang melibatkan dua kondisi:
- Pilih sel yang pemformatannya perlu diterapkan dan buka jendela Aturan Baru.
- Dalam Edit Deskripsi Aturan bidang, masukkan rumus di bawah ini,
=AND(H2>=(TODAY()-45),H2<=(TODAY()-30))
Rumus di atas menggunakan DAN operator menyorot tanggal berwarna kuning yang berumur lebih dari 30 hari tetapi kurang dari 45 hari untuk seluruh pilihan, mulai dari sel no. H2.
C] Menyoroti Akhir Pekan
Pada rentang yang sama, dengan menggunakan rumus WEEKDAY(), tanggal yang jatuh pada akhir pekan juga dapat disorot:
- Pilih sel yang pemformatannya perlu diterapkan dan buka jendela Aturan Baru.
- Dalam Edit Deskripsi Aturan bidang, masukkan rumus di bawah ini,
=WEEKDAY($H2, 2)>5
Itu sintaksis rumus Weekday dapat direpresentasikan sebagai WEEKDAY(serial_number, [return_type]), dimana serial_number mewakili nomor sel dari tanggal yang kita coba periksa (SA1 dalam kasus ini).
Itu tipe_kembali parameter mewakili tipe minggu yang dimulai Senin(1) hingga Minggu(7), dengan mempertimbangkan hari awal minggu sebagai Senin (diwakili oleh 1) dan hari terakhir dalam seminggu sebagai Minggu (diwakili oleh 7).
Namun, return_types bersifat opsional dalam rumus dan diwakili dalam kurung kurawal ketiga. >5 digunakan untuk menyorot Sabtu(6) dan Minggu(7) untuk jenis pengembalian yang ditentukan.
D] Menyorot Baris Berdasarkan Tanggal Tertentu
Opsi ini berguna saat menangani data dalam jumlah besar yang memerlukan penyorotan tanggal tertentu. Hal yang sama dapat dicapai dengan menentukan nilai numerik tanggal atau menggunakan NILAI TANGGAL() fungsi. Nilai numerik harus ditentukan terlebih dahulu untuk menerapkan pemformatan bersyarat untuk tanggal tertentu karena Excel secara permanen menyimpan tanggal sebagai nomor seri berurutan. Untuk menemukan nilai numerik pada tanggal tertentu,
- Klik kanan pada sel dengan tanggal, klik Format Sel >Nomor, lalu pilih Umum . Nilai numerik 5 digit akan ditampilkan pada sel, yang dapat dicatat untuk referensi di masa mendatang.
- Klik Batal setelah nomornya dicatat karena kita tidak perlu mengubah format tanggal sel.
- Klik Pemformatan Bersyarat>Aturan Baru> Pilih Aturan Baru > Gunakan Rumus untuk menentukan sel mana yang akan diformat dan ketik rumus berikut,
=$G5=45249
- Menggunakan NILAI TANGGAL() fungsi, hal yang sama juga dapat dilakukan dengan memasukkan rumus yang disebutkan di bawah ini,
=$G5=DATEVALUE(“11/19/2023”)
Tanda $ yang digunakan di samping C menandakan bahwa kolom akan tetap konstan, namun baris akan berubah-ubah.
Catatan: Urutan nomor urut tanggal-tanggal ini di Excel dimulai dari 1 st Januari 1900 sebagai nomor urut 1. Jadi jika kita memperhitungkan tanggal 19-Nov-2023 maka diberi nomor 45249 karena merupakan 45248 hari setelah 1 st Januari 1900.
pengontrol tidak memiliki cukup sumber daya untuk perangkat ini
Membaca: Cara mengubah Serial Number menjadi Tanggal di Microsoft Excel
4] Menyoroti Hari Libur di Excel
Karena hari libur mungkin bervariasi berdasarkan demografi, Excel tidak memiliki fungsi bawaan untuk menyorot hari libur nasional. Oleh karena itu, untuk mengidentifikasi hari libur, kita perlu mencatat tanggalnya pada kolom yang berbeda dan menerapkannya hitungan rumus untuk memeriksa kemunculan tanggal hari libur di lembar data.
Misalnya, kita memiliki beberapa tanggal yang disebutkan di kolom D dan daftar hari libur di kolom G. Melalui pemformatan bersyarat, kita dapat menyorot hari libur dengan warna MERAH di kolom D, jika ada, dengan membandingkannya dengan tanggal hari libur yang disebutkan di kolom G menggunakan langkah-langkah yang disebutkan di bawah ini:
- Buka Aturan baru kotak dialog dan pilih Gunakan rumus untuk menentukan sel mana yang akan diformat di bawah Pilih Jenis Aturan.
- Masukkan rumusnya =COUNTIF($G:$G,$D3)
- Klik Format > Isi dan pilih warnanya MERAH dari palet dan klik OKE .
Itu hitungan() memeriksa kemunculan tanggal yang disebutkan pada sel G3 – G5 dengan tanggal yang disebutkan pada kolom D dimulai dari sel D3.
5] Pemformatan Tanggal Bersyarat berdasarkan beberapa kondisi
Mari kita asumsikan sebuah lembar kerja dimana kita mempunyai keluhan yang diajukan mengenai berbagai barang rumah tangga. Lembar tersebut mencantumkan tanggal pengaduan diajukan serta tanggal jatuh tempo dan penutupan, sebagaimana berlaku.
Dengan menggunakan conditional formatting, mari kita tandai pengaduan dengan warna PUTIH untuk pengaduan yang sudah DITUTUP, MERAH untuk pengaduan yang sudah melewati tanggal jatuh tempo namun belum ditutup, dan BIRU untuk pengaduan yang belum mencapai tanggal jatuh tempo.
Prosesnya akan melibatkan pembuatan dua aturan pemformatan bersyarat untuk data yang sama, satu untuk data yang belum mencapai tanggal jatuh tempo dan satu lagi untuk data yang telah melampaui tanggal jatuh tempo. Untuk melakukannya,
- Buka jendela New Rule pemformatan bersyarat dan pilih Gunakan rumus untuk menentukan sel mana yang akan diformat di bawah Pilih Jenis Aturan.
- Masukkan rumus yang disebutkan di bawah ini
=AND($I10="",$H10>$K)
- Klik Format , pilih warna BIRU dari palet, dan klik OK.
- Buat yang lain Aturan baru dengan tipe aturan yang sama dan masukkan rumus yang disebutkan di bawah
=AND($I10="",$H10<$K)
- Klik Format, pilih warna MERAH dari palet, dan klik OK.
Pada rumus ini kita cek dulu apakah kolom Tanggal Ditutup KOSONG atau tidak untuk memastikan pengaduan masih terbuka DAN kemudian periksa apakah tanggal jatuh tempo lebih besar dari tanggal sekarang atau lebih kecil untuk menentukan apakah tanggal jatuh tempo sudah dekat ($H10>$K ) atau terlampaui ($H10<$K ) . Tanggal di sel K6 mewakili tanggal saat ini.
6] Menyoroti Tanggal dan Penundaan yang akan datang
Untuk memahami bagaimana kita dapat menyorot penundaan sehubungan dengan tanggal di lembar Excel, mari kita perhatikan contoh di mana kita memiliki daftar saham beserta nama dan tanggal kedaluwarsanya. Melalui pemformatan bersyarat, jika kita perlu menandai yang akan kedaluwarsa dengan warna HIJAU dan yang sudah kedaluwarsa dengan warna MERAH,
- Pilih rentang data dan buka Aturan baru kotak dialog.
- Memilih Format hanya sel yang berisi di bawah Pilih Jenis Aturan
- Di dalam Edit Deskripsi Aturan , memilih Nilai sel , Lebih besar dari , =Sekarang()+30 di dropdown masing-masing.
- Klik Format, pilih HIJAU dari palet warna , dan klik OKE .
- Sekali lagi, buka a Aturan baru dan pilih yang sama Jenis Aturan, seperti di atas.
- Di dalam Edit Deskripsi Aturan , memilih Nilai sel , Kurang dari , =Sekarang() di dropdown masing-masing.
- Klik Format dan pilih MERAH dari palet warna, dan klik OKE .
mencoba menulis ke memori hanya baca
Kesimpulan
Tutorial ini bertujuan untuk membahas secara komprehensif opsi dan fungsi yang dapat digunakan untuk menerapkan Conditional Formatting di lembar kerja Excel berdasarkan tanggal. Ini dapat membantu memahami cara menerapkan aturan pemformatan untuk meningkatkan visualisasi dan analisis data. Menguasai pemformatan bersyarat juga dapat membantu mengidentifikasi tren, memeriksa tenggat waktu, dan menetapkan prioritas tugas, sehingga membantu membuat keputusan yang tepat.
Membaca : Caranya sorot Baris dengan Tanggal menggunakan Pemformatan Bersyarat di Excel
Bagaimana cara membuat pemformatan bersyarat di Excel berdasarkan tanggal?
Pertama, pilih sel tanggal Anda untuk membuat pemformatan bersyarat di Excel berdasarkan tanggal. Klik 'Beranda' > 'Pemformatan Bersyarat' > 'Aturan Baru'. Pilih 'Format sel yang berisi', pilih 'Tanggal Terjadi' di bawah Aturan, pilih kriteria tanggal tertentu, lalu atur format pilihan Anda dengan mengklik 'Format'. Terapkan dengan menekan 'OK'. Ini dengan cepat menyoroti tanggal-tanggal penting dalam data Anda.
Bagaimana cara memformat tanggal jatuh tempo bersyarat di Excel?
Pertama, pilih sel Anda dengan tanggal untuk memformat tanggal jatuh tempo bersyarat di Excel. Kemudian, klik 'Pemformatan Bersyarat' dari tab 'Beranda'. Pilih 'Aturan Baru', pilih 'Format hanya sel yang berisi', tetapkan aturan menjadi 'Nilai Sel' dan 'kurang dari' dan masukkan tanggal jatuh tempo atau rumus Anda. Klik 'Format', pilih gaya pemformatan Anda, dan tekan 'OK' untuk menerapkan.
