Se siete assidui frequentatori di questo blog, probabilmente avrete notato alcuni recenti articoli riguardanti diversi aspetti della formattazione condizionale di Excel. Questi tutorial spiegano come applicare le regole predefinite di Excel oppure come creare una vostra formattazione condizionale basata su formule e/o valori presenti in altre celle.
E adesso sfrutteremo queste conoscenze ormai acquisite per creare fogli di calcolo che rendano evidente la distinzione tra giorni feriali e fine settimana, che evidenzino i giorni festivi e facciano risaltare una prossima scadenza o un ritardo. In altre parole, ci accingiamo ad applicare la formattazione condizionale di Excel alle date.
Se avete qualche conoscenza di base in merito all’utilizzo delle formule in Excel, allora avrete probabilmente familiarità con alcune delle funzioni di data e ora, come ADESSO(), OGGI(), DATA(), GIORNO.SETTIMANA(), ecc. In questo articolo, ci accingeremo a portare queste funzionalità ad un livello ulteriore per formattare le date nel modo desiderato.
La formattazione condizionale di Excel per le date (le regole predefinite)
Microsoft Excel fornisce 10 opzioni per formattare le celle selezionate in base alla data corrente.
- Per applicare la formattazione, è sufficiente andare alla scheda HOME > Formattazione condizionale > Regole evidenziazione celle e selezionare “Data corrispondente a“.
- Selezionate una delle opzioni di data dall’elenco a discesa nella parte sinistra della finestra, che vanno dal mese scorso al mese prossimo.
- Infine, scegliete uno dei formati predefiniti o impostate il formato personalizzato scegliendo opzioni diverse nelle schede “Carattere”, “Bordo” e “Riempimento”. Se la tavolozza standard di Excel non fosse sufficiente, si può sempre fare clic sul pulsante “Altri colori …“.
- Fate clic su OK e godetevi il risultato! 🙂
Tuttavia, questa soluzione veloce e semplice ha due limitazioni significative:
- funziona solo per le celle selezionate
- la formattazione condizionale è sempre applicata in base alla data corrente.
Le formule della formattazione condizionale di Excel per le date
Se volete evidenziare celle o intere righe in base a una data presente in un’altra cella, o creare regole per intervalli di tempo maggiori (vale a dire più di un mese dalla data attuale), dovrete creare una vostra regola di formattazione condizionale basandovi su una formula. A seguire troverete alcuni esempi delle mie formattazioni condizionali preferite per le date.
Come evidenziare i fine settimana in Excel
Purtroppo, Microsoft Excel non dispone di un calendario integrato simile a quello di Outlook. Bene, vediamo come sia possibile creare il proprio calendario automatico con poco sforzo.
Nel progettare il vostro calendario di Excel, è possibile utilizzare la funzione DATA(anno; mese; giorno) per visualizzare i giorni della settimana. Basta inserire l’anno e il numero del mese da qualche parte nel foglio di calcolo e fare riferimento a quelle celle nella formula. Naturalmente, è possibile digitare i numeri direttamente nella formula, ma questo non è un approccio molto efficiente, in quanto vi obbligherebbe a riadattare la formula per ogni mese.
La figura seguente mostra la funzione DATA in azione. Ho usato la formula =DATA($B$2; $B$1; B$4) da copiare poi in tutta la riga 5.
Il calendario di Excel è quasi finito, avete solo bisogno di cambiare il colore dei fine settimana. Naturalmente, non abbiamo intenzione di colorare manualmente le celle. Faremo in modo che sia Excel a formattarle automaticamente, creando una regola di formattazione condizionale che utilizzi la funzione GIORNO.SETTIMANA.
- Iniziamo selezionando la porzione del calendario di Excel in cui si desidera ombreggiare i fine settimana. Nel nostro caso, è l’intervallo $B$4:$AE$11. Assicuratevi di iniziare la selezione con la 1^ colonna della data, colonna B in questo esempio.
- Nella scheda HOME, fate clic sul menu Formattazione condizionale > Nuova regola.
- Create una nuova regola di formattazione sulla base di una formula, come spiegato in Come usare la formattazione condizionale di Excel – Parte 4.
- Nella casella “Formatta i valori per cui questa formula restituisce Vero”, immettete la seguente formula che determinerà quali celle siano sabato o domenica: =GIORNO.SETTIMANA(B$5;2)>5
- Fate clic sul pulsante “Formato…” e impostate il vostro formato personalizzato passando tra le schede “Carattere”, “Bordo” e “Riempimento” e giocate con le diverse opzioni di formattazione. Al termine, fate clic sul pulsante OK per visualizzare in anteprima la regola.
Ora, lasciate che vi spieghi brevemente la funzione GIORNO.SETTIMANA(num_seriale; [tipo_restituito]) in modo che sia possibile per voi adattarla ai vostri fogli di calcolo.
- Il parametro num_seriale rappresenta la data per la quale vogliamo calcolare il giorno della settimana. Si inserisce il riferimento alla prima cella con una data, B$5 nel nostro caso.
- Il parametro [tipo_restituito] determina il tipo di settimana (le parentesi quadre indicano che è un parametro facoltativo). Si inserisce 2 per una settimana che parta da Lunedi (1) a Domenica (7). È possibile trovare l’elenco completo dei tipi restituiti disponibili qui.
- Infine, scrivete >5 per evidenziare solo il sabato (6) e la domenica (7)
Come evidenziare le festività in Excel
Per migliorare ulteriormente il tuo calendario di Excel, è possibile evidenziare anche le festività. Per fare questo, è necessario creare un elenco delle feste che si desidera evidenziare nello stesso o in qualche altro foglio di calcolo.
Ad esempio, ho aggiunto le seguenti festività in colonna A ($A$15:$A$24):
Anche in questo caso apriremo Formattazione condizionale > Nuova regola. Nel caso delle festività, utilizzeremo le funzioni CONFRONTA oppure CONTA.SE:
- =CONFRONTA(B$5;$A$15:$A$24;0)
- =CONTA.SE($A$15:$A$24;B$5)>0
L’immagine seguente mostra il risultato in Excel 2013:
Formattare condizionalmente una cella quando un valore viene modificato in data
Non è un grosso problema formattare una cella quando una data viene inserita in una cella o in un intervallo di celle dove non è consentito inserire nessun altro tipo di valore. In questo caso, si può semplicemente utilizzare una formula per evidenziare le celle non vuote, come descritto in Come usare la formattazione condizionale di Excel – Parte 4. Ma cosa succede se quelle celle hanno già altri valori, ad esempio del testo, e si desidera cambiare il colore di sfondo quando questo venga modificato in una data?
L’obiettivo potrebbe sembrare un po’ complicato da raggiungere, ma in realtà la soluzione è molto semplice.
- Prima di tutto, è necessario determinare il codice del formato della vostra data. Qui ci sono solo alcuni esempi (è possibile trovare l’elenco completo dei codici di data in questo articolo):
- D1: g-mmm-aa oppure gg-mmm-aa
- D2: g-mmm oppure gg-mmm
- D3: mmm-aa
- D4: g/m/aa oppure g/m/aa h.mm oppure gg/mm/aa
- Selezionate una colonna in cui si desidera cambiare il colore delle celle o l’intera tabella nel caso in cui si desideri evidenziare intere righe.
- E ora create una regola di formattazione condizionale utilizzando una formula simile a questa: =CELLA(“formato”;$A2)=”D1″. Nella formula, A è la colonna con le date e D1 è il formato data.
Se la tabella contiene date in 2 o più formati, è possibile utilizzare l’operatore O, ad esempio:
=O(CELLA(“formato”;$A2)=”D1″;CELLA(“formato”;$A2)=”D2″;CELLA(“formato”;$A2)=”D3″)
La figura seguente mostra il risultato di una siffatta regola di formattazione condizionale per le date.
Come evidenziare righe che abbiano una certa data in una determinata colonna
Supponiamo che abbiate un grande foglio di calcolo contenente due colonne di dati (B e C). Vogliamo evidenziare ogni riga avente una certa data, diciamo il 13/05/2015, nella colonna C.
Per applicare la formattazione condizionale di Excel per una data determinata, è necessario prima trovare il suo valore numerico. Come probabilmente saprete, Microsoft Excel immagazzina le date come numeri di serie sequenziali, a partire dal 1° gennaio 1900. Così, il 1/1/1900 è memorizzato come 1, il 2/1/1900 come 2 … e il 13/05/2015 come 42137.
Per trovare il numero corrispondente alla data, fate clic sulla cella, selezionate Formato celle > Numero e scegliete il formato Generale. Annotate il numero che vedete e fate clic su Annulla, in quanto non vogliamo veramente cambiare il formato della data.
Ci resta solo da creare una regola di formattazione condizionale per l’intera tabella con questa formula molto semplice: =$C2=42137. La formula considera che la tabella ha delle intestazioni e che la prima riga con dei dati sia la riga 2.
Un metodo alternativo è quello di usare la funzione DATA.VALORE che converte la data nel formato numerico con cui viene memorizzata, ad esempio =$C2=DATA.VALORE(“13/05/2015”). Il risultato sarà il medesimo.
Formattare condizionalmente le date in base alla data corrente
Come probabilmente sapete Microsoft Excel dispone della funzione OGGI() per effettuare vari calcoli basati sulla data corrente. Qui mostriamo solo alcuni esempi di come si possa utilizzare con la formattazione condizionale di Excel.
Esempio 1. Evidenziare le date pari, maggiori o inferiori alla data odierna
Per formattare condizionalmente alcune celle o intere righe in base alla data odierna, è possibile utilizzare la funzione OGGI come segue:
- Uguale a oggi: =$B2=OGGI()
- Maggiore di oggi: =$B2>OGGI()
- Minore di oggi: =$B2<OGGI()
La schermata qui sotto mostra le regole di cui sopra in azione. Al momento di scrivere questo articolo OGGI era il 17-Giu-2015.
Esempio 2. Formattare le date in base a molteplici condizioni
In modo simile, è possibile utilizzare la funzione OGGI in combinazione con altre funzioni di Excel per gestire scenari più complessi. Potreste ad esempio volere che la formattazione condizionale evidenzi la colonna “Fattura” quando la data di consegna è uguale o maggiore rispetto a oggi, ma, nel contempo, che la formattazione scompaia quando si immette il numero di fattura.
Per questo compito, avremo bisogno di una colonna aggiuntiva con la seguente formula (dove B è la colonna di consegna e C la colonna con il numero di fattura):
=SE(B2>=OGGI();SE(C2=""; 1; 0); 0)
Se la data di consegna è maggiore o uguale alla data corrente e non c’è un numero nella colonna fattura, la formula restituisce 1, altrimenti 0.
Dopo di che si crea una semplice regola di formattazione condizionale per la colonna “Fattura” con la formula =$D2=1 dove D è la vostra colonna aggiuntiva. Naturalmente, la colonna in seguito potrà essere nascosta.
Esempio 3. Evidenziare prossime scadenze e ritardi
Supponiamo di avere in Excel la pianificazione di un progetto, che elenca le attività, le loro date di inizio e la durata. Quello vorremmo è di avere la data di fine per ogni attività calcolata automaticamente. La formula dovrebbe però anche tenere in considerazione i fine settimana. Ad esempio, se la data di partenza è 12-Giu-2015 e il numero di giorni di lavoro (durata) è 2, la data finale dovrebbe risultare 16-Giu-2015, perché 13/06 e 14/06 sono Sabato e Domenica.
Per fare questo, useremo la funzione GIORNO.LAVORATIVO.INTL(Data_iniziale; Giorni; [Festivi]; [Vacanze]), più precisamente =GIORNO.LAVORATIVO.INTL(B2; C2;1).
Nella formula, abbiamo inserito 1 come il 3° parametro poiché indica Sabato e Domenica come festività. È possibile utilizzare un altro valore se i vostri week-end fossero diversi, ad esempio, Venerdì e Sabato. L’elenco completo dei valori di fine settimana è disponibile qui. In più, è anche possibile utilizzare il 4° parametro [Vacanze], che è un elenco di festività (intervallo di celle), che dovrebbero essere escluse dal calendario lavorativo.
E, infine, si consiglia di evidenziare le righe a seconda di quanto sia lontana la scadenza. Per esempio, le regole di formattazione condizionale basate sulle seguenti due formule evidenziano rispettivamente date di completamento imminenti e recenti:
- =E($D2-OGGI()>=0; $D2-OGGI()<=7) – evidenzia tutte le righe in cui la data di completamento (colonna D) è entro i prossimi 7 giorni. Questa formula è molto utile quando si tratta di monitorare date di scadenza o pagamenti imminenti.
- =E(OGGI()-$D2>=0; OGGI()-$D2<=7) – evidenzia tutte le righe in cui la data di completamento (colonna D) è negli ultimi 7 giorni. È possibile utilizzare questa formula per monitorare gli ultimi pagamenti scaduti e altri ritardi.
Ecco alcuni altri esempi di formule che possono essere applicati alla tabella precedente:
- =$D2<OGGI() – mette in evidenza tutte le date trascorse (cioè date inferiori alla data corrente). Può essere usato per formattare abbonamenti scaduti, pagamenti scaduti ecc.
- =$D2>OGGI() – mette in evidenza tutte le date future (cioè date superiori alla data corrente). Si può usare per evidenziare prossimi eventi.
Certo, ci possono essere variazioni infinite alle formule di cui sopra, a seconda del particolare compito che dobbiamo svolgere. Per esempio:
- =$D2-OGGI()>=6 – mette in evidenza le date che si verificano entro 6 o più giorni.
- =$D2=OGGI()-14 – mette in evidenza le date che si sono verificate esattamente 2 settimane fa.
Come evidenziare le date all’interno di un intervallo di date
Se si dispone di un lungo elenco di date nel foglio di lavoro, potreste voler evidenziare le celle o le righe che rientrano in un determinato intervallo di date, ovvero evidenziare tutte le date che si trovano tra due date.
È possibile compiere questa operazione utilizzando nuovamente la funzione OGGI(). Dovrete solo costruire formule un po’ più elaborate, come mostrato negli esempi qui sotto.
Formule per evidenziare date passate
- Più di 30 giorni fa: =OGGI()-$A2>30
- Da 30 a 15 giorni fa, inclusi: =E(OGGI()-$A2>=15; OGGI()-$A2<= 30)
- Meno di 15 giorni fa: =E(OGGI()-$A2>=1; OGGI()-$A2<15)
La data corrente e le eventuali date future non sono colorate.
Formule per evidenziare date future
- Più di 30 giorni da oggi: =$A2-OGGI()>30
- Da 30 a 15 giorni, inclusi: =E($A2-OGGI()>=15; $A2-OGGI()<=30)
- Meno di 15 giorni: =E($A2-OGGI()>=1; $A2-OGGI()<15)
La data corrente e le eventuali date passate non sono colorate.
Come ombreggiare gap e intervalli di tempo
In questo ultimo esempio, ci accingiamo a utilizzare un’altra funzione di Excel – =DATA.DIFF(data_iniziale; data_finale; “intervallo”). Questa funzione calcola la differenza tra due date in base all’intervallo specificato. Si differenzia da tutte le altre funzioni che abbiamo discusso in questo tutorial in quanto ti permette di ignorare mesi o anni e calcolare la differenza solo tra giorni o mesi, a seconda di quale opzione si sceglie.
Non vedete come questo potrebbe servirvi? Pensateci in un altro modo… Supponete di avere una lista dei compleanni dei vostri familiari e amici. Volete sapere quanti giorni ci sono per il loro prossimo compleanno? Inoltre, quanti giorni sono rimasti esattamente al vostro anniversario di matrimonio o ad altri eventi che non vorreste perdere?
La formula che vi serve è la seguente (dove A è la vostra colonna “Data”):
=DATA.DIFF(OGGI();DATA((ANNO(OGGI())+1);MESE($A2);GIORNO($A2));"yd")
Il tipo di intervallo “yd” alla fine della formula viene utilizzato per ignorare gli anni e calcolare la differenza solo tra i giorni. Per l’elenco completo delle tipologie di intervallo e per una disamina completa della funzione, consultate pure questo articolo.
E ora creiamo una regola di formattazione condizionale di Excel per ombreggiare gap in diversi colori. In questo caso, è più sensato utilizzare “Scale di colori” (Formattazione condizionale > Scale di colori), piuttosto che creare una regola separata per ogni gap.
La figura seguente mostra il risultato in Excel – un gradiente “scala a 3 colori” con sfumature dal verde al rosso al giallo.
Speriamo che, almeno uno delle formattazioni condizionali di Excel per le date discusse in questo articolo vi sia tornata utile. Grazie per la lettura!
- ADESSO – Scheda tecnica
- CELLA – Scheda tecnica
- CONFRONTA – Scheda tecnica
- CONTA.SE – Scheda tecnica
- DATA – Scheda tecnica
- DATA.DIFF – Scheda tecnica
- DATA.VALORE – Scheda tecnica
- E – Scheda tecnica
- GIORNO.LAVORATIVO.INTL – Scheda tecnica
- GIORNO.SETTIMANA – Scheda tecnica
- O – Scheda tecnica
- OGGI – Scheda tecnica
- SE – Scheda tecnica
