期限切れなのに未完了の行だけ、赤くする方法

完成すると、期限超過の2行だけ赤くなる

次のような管理表を例にします。期限は「今日」を基準にした相対的な日付で、配布用のGoogle Sheetsでも日付が自動更新される設定です。

この設定では「進行中」もまだ終わっていない作業として扱います。一方、昨日が期限でも「完了」とした行は赤くしません。今日が期限の行もまだ期限切れではありません。

まずA列〜D列に案件・期限・状態を用意する

1行目に「案件」「担当」「期限」「状態」の見出しを作り、2行目からデータを入力します。C列は必ずGoogleスプレッドシートが日付として認識できる値にしてください。「2日前」という文字列を直接入力するのではなく、実際の日付を入力します。

試したい場合、C2に =TODAY()-2、C3に =TODAY()-1、C4に =TODAY()+2、C5に =TODAY()、C7に =TODAY()-1 と入力し、C6は空欄にします。上の表と同じ結果になります。

状態は「未完了」「進行中」「完了」など、普段の管理表で使っている文字を入れます。この例では 「完了」だけを除外します。

行全体を赤くする条件付き書式

1. A2:D100 を選択します。色を付けたい列をすべて含め、見出し行は選択しません。
2. 上部メニューの ［表示形式］→［条件付き書式］ を開きます。
3. 右側に表示される［セルの書式設定の条件］から［カスタム数式］を選び、次の式を入力します。
4. 書式設定スタイルで淡い赤を選び、［完了］を押します。

=AND(ISNUMBER($C2),$C2<TODAY(),$D2<>"完了",$A2<>"")

この式は、期限が正しい日付で、今日より前で、状態が「完了」ではなく、案件名が空ではない場合にだけTRUEになります。Google公式の条件付き書式ヘルプでも、カスタム数式で他の列を参照し、行全体に書式を設定する方法が案内されています。

ここが重要： $C2 の $ は列を固定しています。行番号まで $C$2 と固定すると、すべての行が同じC2だけを見るため、誤った色付けになります。

期限切れの残件数も自動で数える

赤い行を探すだけでなく、まだ対応が必要な件数も把握したい場合は、F1に「未完了の期限超過」、G1に次の式を入力します。

=COUNTIFS(A2:A100,"<>",C2:C100,">0",C2:C100,"<"&TODAY(),D2:D100,"<>完了")

サンプルでは結果が2になります。納品書作成のD2を「完了」に変えると1になり、条件付き書式の赤色も消えます。D2を「未完了」に戻せば2です。この変化は実際のGoogle Sheetsでも確認しています。

この件数式では、C列が正の数値であることを確認しています。通常の日付は数値として保存されますが、日付以外の数値をC列に入れている場合は正しく数えられません。C列には日付だけを入力してください。

思った通りに赤くならないとき

期限が過ぎているのに白いまま

C列の見た目が日付でも、実体が文字列になっている場合があります。元の値を確認し、日付として入力し直してください。書式だけ日付に変えても直らないことがあります。

完了したのに赤い

状態の表記が「完了」ではなく「済」「完了済」などになっていないか確認。式に書いた文字と一致させます。

すべての行が赤くなった

適用範囲が A2:D100 か確認します。式の参照が $C$2 でなく $C2 になっているかも確認してください。

期限当日も赤くしたい

今日も対象にする運用なら $C2<TODAY() を $C2<=TODAY() に変更します。残件数の式も "<"&TODAY() から "<="&TODAY() に揃えます。

数式と行の色で判定が違う

範囲の開始行、期限列、状態列、空欄判定を照合してください。色付けの式とCOUNTIFSの式は同じ条件を意図しているため、列や対象行がずれていないかが最優先です。