Excel

Tarikh pemformatan bersyarat telah jatuh tempo

Conditional Formatting Date Past Due

Rumusan Excel: Tarikh pemformatan bersyarat telah jatuh tempoFormula generik
=(date2-date1)>n
Ringkasan

Untuk menyoroti tarikh yang 'jatuh tempo', anda boleh menggunakan peraturan pemformatan bersyarat yang memeriksa apakah perbezaan antara dua tarikh lebih besar daripada jumlah hari tertentu. Dalam contoh yang ditunjukkan, tiga peraturan pemformatan bersyarat telah diterapkan pada julat D5: C12 dengan formula berikut:





 
=(D5-C5)<3 // rule 1 (green) =(D5-C5)<10 // rule 2 (yellow) =(D5-C5)>=10 // rule 3 (red)

Catatan: peraturan pemformatan bersyarat dinilai relatif terhadap sel kiri atas dalam pemilihan pada saat peraturan dibuat, dalam hal ini D5.

Mengira varians dalam beberapa hari

Perbezaan dalam lajur E dikira dengan mengurangkan tarikh asal dari tarikh semasa dengan formula ini:





 
=D5-C5

Hasilnya adalah perbezaan hari antara tarikh asal dan tarikh semasa. Perbezaan positif menunjukkan tarikh akhir (iaitu 'slip' dalam jadual). Perbezaan negatif menunjukkan tarikh semasa lebih awal dari jadual. Ini berfungsi, kerana Tarikh Excel adalah nombor siri .

bagaimana saya boleh belajar formula excel

Varians yang ditunjukkan dalam lajur E hanya untuk rujukan dalam contoh ini, dan tidak digunakan oleh peraturan pemformatan bersyarat. Walau bagaimanapun, jika anda menganggap lajur E sebagai tiang penolong , anda boleh menulis peraturan pemformatan bersyarat yang lebih mudah yang menggunakan varians secara langsung.



Penjelasan

Dalam contoh ini, kami ingin menerapkan tiga warna yang berbeza, bergantung pada berapa tarikh asal berbeza dari tarikh semasa:

  1. Hijau jika variansnya kurang dari 3 hari
  2. Kuning jika jarak antara 3 dan 10 hari
  3. Merah jika varians lebih besar daripada 10 hari

Untuk setiap peraturan, kami mengira varians dengan mengurangkan tarikh asal dari tarikh 'semasa' (seperti yang dijelaskan di atas). Kemudian kami memeriksa hasilnya dengan ungkapan logik. Apabila ungkapan mengembalikan BENAR, pemformatan bersyarat dipicu.

Kerana kami mahukan tiga warna yang terpisah, masing-masing dengan ujian logik, kami memerlukan tiga peraturan pemformatan bersyarat yang berasingan. Layar di bawah menunjukkan bagaimana peraturan telah dikonfigurasi untuk menerapkan format hijau, kuning, dan merah. Perhatikan dua peraturan pertama telah 'berhenti jika benar' dicentang:

Peraturan untuk memformat tarikh jatuh tempo

Peraturan dinilai mengikut urutan yang ditunjukkan. Peraturan 1 menguji jika variansnya kurang dari 3 hari. Peraturan 2 memeriksa apakah varians kurang dari 10 hari. Peraturan 3 memeriksa apakah varians lebih besar daripada atau sama dengan 10 hari. Kedua peraturan 1 dan peraturan 2 telah diaktifkan 'stop if true'. Apabila mana-mana peraturan mengembalikan BENAR, Excel akan berhenti memeriksa peraturan tambahan.

Tertunda oleh hari n dari hari ini

Anda mungkin mahu membandingkan tarikh akhir dengan tarikh hari ini. Untuk menguji sama ada tarikh terlambat sekurang-kurangnya n hari dari hari ini, anda boleh menggunakan formula seperti ini:

 
=( TODAY ()-date)>=n

Formula ini akan mengembalikan BENAR hanya apabila tarikh sekurang-kurangnya n hari yang lalu. Apabila tarikh di masa depan, perbezaannya adalah angka negatif, jadi peraturan tidak akan berlaku.

Untuk maklumat lebih lanjut mengenai kriteria formula bangunan, lihat 50+ contoh kriteria formula .

Permulaan pantas | Lebih Banyak Contoh | Penyelesaian masalah | Latihan Lampiran Fail tarikh pemformatan bersyarat lewat tarikh matang.xlsx Pengarang Dave Bruns


^