Mbak @Lala ...
[1]. Untuk yang pertama, aturannya simple saja. Karena pembandingnya adalah nilai pada kolom J (DUE DATE), maka pastikan kolomnya bersifat absolut. Sedangkan kolom yang dibandingkan (area departemen) bersifat relatif. Sehingga jika dibuatkan formula menjadi:
=AND(ISNUMBER(O3);O3>$J3)
Kolom O dibuat relatif oleh karena saat Conditional Formatting-nya di terapkan (Applies To) ke range $O$3:$AU$3949, Excel akan otomatis memindahkan alamat sel masing-masing sel referensi (dalam hal ini kolom O), baik ke kanan (berdasarkan kolom) atau ke bawah (berdasarkan baris). Sedangkan kolom J, oleh karena bersifat tetap (absolut) pada kolomnya, hanya akan berpindah ke bawah (berdasarkan baris). Sehingga dengan formula tersebut, Excel akan membandingkan setiap nilai dari kolom O sampai dengan kolom AU, baris ke-3 (sel O3) sampai dengan baris ke-3949 (sel AU3949) apakah lebih besar dari nilai pada kolom J baris ke-3 (sel J3) sampai dengan baris ke-3949 (sel J3949).
Semoga penggunaan alamat relatif dan absolut ini dapat dipahami agar dapat diterapkan pada Conditional Formatting lainnya.
[2]. Untuk yang kedua, sel G1 dan H1 memang digunakan sebagai acuan range waktu yang akan diperiksa. Kalau dicari di sheet LOOKUP DATA, memang tidak ada. Nilai dari kedua sel tersebut tersimpan pada nama range Lookup.First dan Lookup.Last. Oleh karena saya menghindari penggunaan Array Formula, maka solusi yang saya gunakan adalah dengan menggunakan kolom bantu pada sheet TEMPLATE kolom AW.
Pada kolom tersebut, akan dilakukan perbandingan nilai pada kolom O sampai dengan kolom AU untuk setiap barisnya, apakah ada tanggal pada range tersebut yang masuk kondisi yang ditentukan. Apabila ada tanggal yang masuk kondisi, maka formula akan menghasilkan nilai indeks baris, selain itu formula akan menghasilkan baris kosong.
Nilai-nilai pada range AW3:AW3949 dinamakan Lookup.Helper. Nilai yang ada pada nama range tersebut yang kemudian digunakan oleh formula pada sheet TEMPLATE. Intinya, karena nilai pada nama range tersebut berisi indeks baris, maka nilai-nilainya otomatis tersusun secara ascending, meskipun ada kemungkinan nilainya tidak berurutan (misalkan berupa 1, 2, 5, 6, 7, 11 ...). Karena nilainya sudah tersususn ascending, maka fungsi SMALL digunakan untuk mengambil indeks baris dari data yang cocok (sesuai kondisi).
Saya tidak tahu dimana letak hasil yang tidak sesuai, karena acuan saya adalah nilai-nilai yang tertera di lampiran file yang mbak berikan pertama-tama. Mungkin mbak @Lala bisa menunjukkan masalahnya dimana, mungkin dengan memberikan contoh hasil secara manual?