Formula umum
=(date2-date1)>n
Ringkasan
Untuk menyoroti tanggal yang "lewat jatuh tempo", Anda dapat menggunakan aturan pemformatan bersyarat yang memeriksa apakah varians antara dua tanggal lebih besar dari jumlah hari tertentu. Dalam contoh yang diperlihatkan, tiga aturan pemformatan bersyarat telah diterapkan ke rentang D5: C12 dengan rumus berikut:
=(D5-C5)<3 // rule 1 (green) =(D5-C5)=10 // rule 3 (red)
Catatan: aturan pemformatan bersyarat dievaluasi relatif terhadap sel kiri atas dalam pilihan pada saat aturan dibuat, dalam hal ini D5.
Menghitung varians dalam beberapa hari
Varians di kolom E dihitung dengan mengurangi tanggal asli dari tanggal sekarang dengan rumus ini:
=D5-C5
Hasilnya adalah perbedaan hari antara tanggal asli dan tanggal sekarang. Perbedaan positif menunjukkan tanggal yang terlambat (yaitu "slip" dalam jadwal). Perbedaan negatif menunjukkan tanggal saat ini lebih cepat dari jadwal. Ini berfungsi, karena tanggal Excel adalah nomor seri.
Varians yang diperlihatkan di kolom E hanya untuk referensi dalam contoh ini, dan tidak digunakan oleh aturan pemformatan bersyarat. Namun, jika Anda memperlakukan kolom E sebagai kolom pembantu, Anda bisa menulis aturan pemformatan bersyarat yang lebih sederhana yang menggunakan varians secara langsung.
Penjelasan
Dalam contoh ini, kami ingin menerapkan tiga warna berbeda, bergantung pada seberapa banyak tanggal asli bervariasi dari tanggal saat ini:
- Hijau jika variansnya kurang dari 3 hari
- Kuning jika variansnya antara 3 dan 10 hari
- Merah jika varians lebih besar dari 10 hari
Untuk setiap aturan, kami menghitung varians dengan mengurangi tanggal asli dari tanggal "saat ini" (seperti dijelaskan di atas). Kemudian kami memeriksa hasilnya dengan ekspresi logis. Saat ekspresi mengembalikan TRUE, pemformatan bersyarat dipicu.
Karena kami menginginkan tiga warna terpisah, masing-masing dengan pengujian logis, kami memerlukan tiga aturan pemformatan bersyarat yang terpisah. Layar di bawah ini menunjukkan bagaimana aturan telah dikonfigurasi untuk menerapkan format hijau, kuning, dan merah. Perhatikan bahwa dua aturan pertama memiliki tanda "stop if true":
Aturan dievaluasi dalam urutan yang ditampilkan. Aturan 1 menguji jika variansnya kurang dari 3 hari. Aturan 2 memeriksa apakah variansnya kurang dari 10 hari. Aturan 3 memeriksa apakah varians lebih besar dari atau sama dengan 10 hari. Baik aturan 1 dan aturan 2 mengaktifkan "hentikan jika benar". Saat salah satu aturan mengembalikan TRUE, Excel akan berhenti memeriksa aturan tambahan.
Terlambat selama n hari dari hari ini
Anda mungkin ingin membandingkan tanggal jatuh tempo dengan tanggal hari ini. Untuk menguji apakah tanggal telah lewat setidaknya n hari dari hari ini, Anda dapat menggunakan rumus seperti ini:
=(TODAY()-date)>=n
Rumus ini akan mengembalikan TRUE hanya jika tanggal setidaknya adalah n hari yang lalu. Jika tanggal di masa mendatang, selisihnya adalah angka negatif, sehingga aturan tidak akan pernah aktif.
Untuk informasi selengkapnya tentang membangun kriteria rumus, lihat 50+ contoh kriteria rumus.
