Applicare le regole della Formattazione condizionale al formato data

Applicare la Formattazione condizionale per distinguere fra giorni feriali, festivi e fine settimana oppure evidenziare e controllare scadenze o ritardi.
Sono davvero molte le condizioni nella quali può rendersi utile monitorare delle scadenze relative a delle date imputate sul Foglio Excel.
La Formattazione condizionale e l'inserimento di alcune Formule permette di applicare sulle date presenti nei Fogli Excel controlli e monitoraggi su eventi e scadenze.

Contenuti del tutorial: Applicare la Formattazione condizionale alle date di Excel

Nel presente tutorial viene spiegato come applicare la Formattazione condizionale di Excel al formato data. Passo per passo vengono illustrate le impostazione e proposti alcuni esempi di applicazione.

[1] - Formattazione condizionale, Regola evidenziazione celle, Data corrisponde a...
[2] - Applicare la Formattazione condizionale alle date attraverso Formule e Funzioni
[3] - Evidenziare Festività e Vacanze con la formattazione condizionale
[4] - Formattare le date: uguali, precedenti o successive alla data attuale
[5] - Monitorare scadenze con la formattazione condizionale

Formattazione condizionale, regola evidenziazione celle.
Data corrispondente a...

La prima operazione che Excel mette a disposizione per la Formattazione condizionale delle date è disponiible nel gruppo Regole evidenziazione celle.

Partendo dal pulsante Formattazione condizionale presente sulla scheda Home, fare clic su Regole evidenziazione celle e successivamente scegliere Data corrispondente a... (vedi immagine sottostante).

Scelta l'opzione Data corrispondente a.... si apre l'omonima Finestra di dialogo che permette di scegliere fra numerose opzioni predefinite: Ieri; Oggi; Domani; Ultimi 7 giorni; Settimana scorsa; Questa settimana; Settimana prossima; Mese scorso; Questo mese e Mese prossimo.

Questo tipo di impostazione, seppure di facile applicazione non è molto utilizzato poichè mette a disosizione regole rigide che vanno a valere solo per i dati presenti nelle celle selezionate.
Non è infatti possibile individuare riferimenti ad altre celle del Foglio di lavoro o applicare Formule e Funzioni.

Esempio di applicazione della Formattazione condizionale: Data corrispondente a....

L'impostazione di questa formattazione è molto semplice. Una volta selezionato sul Foglio di lavoro l'intervallo di celle contenente le date, nella scheda Home fare clic su Formattazione condizionale, quindi scegliere Regola evidenziazione celle e successivamente Data corrisponde a...
Nella successiva Finestra dialogo (vedi immagine) scegliere ad esempio Settimana prossima e individuare la colorazione fra i formati predefiniti oppure passare all'opzione personalizza.
Una volta confermato con OK, le date corrispondenti alla settimana successiva vengono formattate con il colore e proprietà precedentemente impostato.

Applicare la Formattazione condizionale alle date con Formule e Funzioni

Se si vogliono formattare delle celle sulla base dei valori presenti in altre parti del Foglio di lavoro di Excel, o se si vogliono evidenziare intervalli di tempo diversi da quanto proposto dalle opzioni di Data corrispondente a... è necessario ricorre alla definizione di Nuove regole di formattazione.

Per definire una Formattazione condizionale attraverso delle Formule, nella scheda Home fare clic sul pulsante Formattazione condizionale e successivamente scegliere Nuova regola...

Una volta cliccato sull'opzione Nuova regola... si apre la Finestra di dialogo Nuova regola di Formattazione attraverso la quale è possibile definire ex novo una regola.
La Finestra di dialogo ripropone le stesse opzioni che si ottengono cliccando nei vari menu della Formattazione condizionale (Regole, Barre dati etc.) selezionando l'opzione: altre regole.

Le possibilità che Excel mette a disposizione nella Finestra di dialogo Nuove regole di formattazione sono le seguenti: Formatta tutte le celle in base ai relativi valori; Formatta solo le celle che contengono; Formatta solo i primi o gli ultimi valori; Formatta solo i valori superiori o inferiori alla media; Formatta solo i valori univoci o duplicati; Utilizza una formula per determinare le celle da formattare.

Per passare a definire la Formattazione condizionale attraverso l'impostazione di una Formula, scegliere l'opzione: Utilizza una formula per determinare le celle da formattare.

Evidenziare i giorni Festivi con la formattazione condizionale

Per questo tipo di impostazione risulta fondamentale conoscere e applicare la Funzione GIORNO.SETTIMANA
:: La Funzione GIORNO.SETTIMANA restituisce il giorno della settimana corrispondente ad una certa data. In base all'impostazione di default i giorni della settimana vengono restituiti dalla Funzione con un numero intero compreso tra 1 (la domenica) e il 7 (sabato).

:: per un approfondimento sulla Funzione GIORNO.SETTIMANA si rimanda a:  Excel: Calcolare il giorno della settimana e la data di una scadenza. Le Funzioni: GIORNO.SETTIMANA; DATA.MESE e FINE.MESE

Applicare una specifica formattazione al sabato e alla domenica presenti in un elenco di date

Esempio. Si vogliono formattare con testo di colore rosso le giornate di sabato e domenica presenti in un intervallo di celle.

Come prima operazione selezionare la prima cella dell'intervallo. Successivamente nella scheda Home fare clic sul pulsante Formattazione condizionale e scegliere l'opzione Nuova regola...

Nella Finestra di dialogo Nuova regola di formattazione selezionare l'opzione: Utilizza una formula per determinare le celle da formattare. Questa opzione attiva il sottostante campo Formatta i valori per cui questa formula restituisce Vero: nel quale va inserita una Formula la cui eventuale restituzione Vero permette di individuare le celle da formattare.

Nell'esempio proposto è stata inserita la Formula
=O(GIORNO.SETTIMANA(B2)=1;GIORNO.SETTIMANA(B2)=7)
Nella quale: il connettivo logico di disgiunzione O permette di verificare se il giorno della settimana di una data è il numero 7 (sabato) oppure il numero 1 (domenica).
Qualora una di queste comparazioni risultasse vera viene formattata la cella.
Nella sottostante sezione, cliccando sul pulsante Formato... è stato impostato un testo di colore rosso. Il formato del testo che verrà applicato risulta visibile nella finestra dell'Anteprima.

Per adattare la Formattazione condizionale applicata alla cella B2 anche alle sottostanti celle, trascinare il pulsante di riempimento della cella tenendo premuto il tasto destro del mouse. Una volta rilasciato il pulsante, nel menu contestuale che Excel propone scegliere l'opzione Ricopia solo formattazione. (Vedi immagine sottostante).

Evidenziare Festività e Vacanze con la formattazione condizionale

Le Festività (politiche e religiose) e le Vacanze, sono giorni non lavorativi che spesso non coincidono con il sabato e domenica.
Definire un criterio logico per individuare queste date è impossibile, pertanto bisogna creare sul Foglio di lavoro un elenco contenente le date che si vogliono considerare come Festive o Vacanze.

Nell'esempio raffigurato nella sottostante immagine, nell'intervallo celle D2:D5 sono state descritte tre giornate Festive. Nella colonna A del Foglio di lavoro si vuole evidenziare la presenza di queste festività con una colorazione blu del testo.

Per procedere con questa formattazione, nella scheda Home fare clic sul pulsante Formattazione condizionale e scegliere l'opzione Nuova regola...

Nella successiva Finestra di dialogo Nuova regola di formattazione selezionare l'opzione: Utilizza una formula per determinare le celle da formattare. e nel sottostante campo Formatta i valori per cui questa formula restituisce Vero: inserire la seguente Formula.
=CONTA.SE($D$2:$D$4;A1)>0
Questo particolare confronto, permette di verificare (contare) se i valori presenti nell'intervallo $D$2:$D$4 sono presenti (>0) anche nella cella A1 e successive.
Prima di confermare con OK l'impostazione della Funzione, con il pulsante Formato... definire la formattazione di colore blu da applicare se la Funzione restituisce VERO come risultato.

Una volta applicata la Formattazione condizionale, per condividere la formattazione con tutte le celle sottostanti, trascinare il pulsante di riempimento con il tasto destro del mouse e nel menu contestuale scegliere l'opzione: Ricopia solo formattazione.

In alternativa alla Funzione CONTA.SE, per questo tipo di Formattazione potrebbe essere applicata anche la Funzione CONFRONTA.
Nello specifico. la  Formula da applicare è la seguente
=CONFRONTA(A1;$D$2:$D$4;0)

Formattare le date: uguali, precedenti o successive alla data attuale

Sempre utilizzando la Finestra di dialogo Nuove regole di formattazione, e la successiva opzione: Utilizza una formula per determinare le celle da formattare è possibile inserire delle semplici Formule che permettano di formattare delle date prima o dopo la data attuale. Oppure formattare le celle che contengono una data che corrisponde alla data di oggi.

Considerando di avere il valore da formattare nella cella A1:
- Per formattare le celle che contengono la data attuale inserire la Funzione: =A1=OGGI()
- Per formattare le celle contenete date successive alla data attuale: =A1>OGGI()
- Per formattare le celle contenete date precedenti alla data attuale: =A1<OGGI()

Prima di confermare OK, nella parte sottostante della Finestra di dialogo tramite il pulsante Formato... impostare la formattazione che si vuole assegnare alle celle quando le Formule di cui sopra restituisco vero.
Una volta impostata la Formattazione è possibile prenderne visione nel campo Anteprima.

Monitorare scadenze con la formattazione condizionale

Nei Fogli Excel aziendali si verifica frequentemente la condizione di dover monitorare scadenze che possono essere di produzione, consegna, certificazione, pagamenti etc.
La Formattazione condizionale può diventare un eccellente strumento per monitorare questi impegni e creare degli "allert" colorati che ci notificano una scadenza prossima oppure già avvenuta.

Esempio di monitoraggio scadenze con la Formattazione condizionale

Nell'esempio proposto nella successiva immagine. Nella colonna A sono state inserite una serie di date rispetto le quali si vuole controllare una scadenza a 60 giorni.
Rispetto la data attuale presente nella casella di testo si vogliono creare due distinte formattazioni una di colore giallo quando mancano meno di 10 giorni alla scadenza e una di colore rosso (scadenza avvenuta).
Nell'ordine, la prima regola che deve essere definita è quella di monitorare i 50 giorni data con il colore gialla (i dieci giorni prima delle scadenza)

Per procedere: quale prima azione selezionare la cella A1.
Poi nella scheda Home cliccare su Formattazione condizionale e scegliere Nuova regola. Nella successiva Finestra di dialogo scegliere: Utilizza una formula per determinare le celle da formattare.

Come prima regola è stato impostato il seguente test comparativo.
=A1+50<OGGI()
Cliccare sul pulsante Formato... e impostare un colore si sfondo giallo che verrà applicato quando il test risulta vero.
Effettuata questa prima impostazione, ripetere l'operazione (sempre partendo dalla cella A1) inserendo la Formula:
=A1+60<OGGI()
In questo secondo caso, cliccare sul pulsante Formato... e impostare uno sfondo rosso chiaro.

Per applicare la formattazione a tutte le celle sottostanti, trascinare il pulsante di riempimento tenendo premuto il tasto destro del mouse e nel menu contestuale scegliere l'opzione: Ricopia solo formattazione.

Per verificare la regola di formattazione applicata, scegliere una o tutte le celle dell'intervallo dati, e cliccare sul pulante Formattazione condizionale, scegliendo l'opzione Gestisci regole... per accedere alla Finestra di dialogo: Gestione regola formattazione condizionale

Nella Finestra di dialogo Gestisci regole formattazione condizionale è possibile duplicare, eliminare o modificare una regola.
Nella Finestra è anche possibile prendere visione delle regole e nel caso specifico verificarne l'ordine e la priorità gerarchica. L'ultima regola inserita (quella nella prima riga) è la regola principale.

Approfondimento consigliato. Tutorial: La Formattazione condizionale di Excel

Indice Generale Argomenti: EXCEL

[Valter Borsato: marzo - 2016 | Ultimo aggiornamento 21/06/2023]