#Excel – Come usare la formattazione condizionale di Excel – Parte 5

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.

  1. Per applicare la formattazione, è sufficiente andare alla scheda HOME > Formattazione condizionale > Regole evidenziazione celle e selezionare “Data corrispondente a“.
    Le regole predefinite di formattazione condizionale per le date in Excel
  2. Selezionate una delle opzioni di data dall’elenco a discesa nella parte sinistra della finestra, che vanno dal mese scorso al mese prossimo.
  3. 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 …“.
    Scegliete uno dei formati predefiniti oppure un formato personalizzato
  4. Fate clic su OK e godetevi il risultato! 🙂
    Evidenzia le celle con date del mese scorso

Tuttavia, questa soluzione veloce e semplice ha due limitazioni significative:

  1. funziona solo per le celle selezionate
  2. 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.

Usare la funzione DATA di Excel per mostrare i giorni della settimana

INFO
Se desiderate visualizzare solo il giorno della settimana, come mostrato nell’immagine sopra, selezionate le celle con la formula (riga 5 nel nostro caso), fate clic destro e selezionate Formato celle … > Scheda “Numero” > Personalizzato. Dall’elenco a discesa sotto “Tipo”, selezionate “ggg” per mostrare i nomi dei giorni abbreviati, oppure “gggg” per mostrarli completi. Se le due opzioni non fossero presenti, è sufficiente inserirle manualmente nella casella di testo “Tipo”.

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.

  1. 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.
  2. Nella scheda HOME, fate clic sul menu Formattazione condizionale > Nuova regola.
  3. Create una nuova regola di formattazione sulla base di una formula, come spiegato in Come usare la formattazione condizionale di Excel – Parte 4.
  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
  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.

Utilizzate il pulsante Formato per impostare un formato personalizzato

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.

LEGGI:  #Excel – Come usare le matrici e le formule matriciali – Parte 3

Ad esempio, ho aggiunto le seguenti festività in colonna A ($A$15:$A$24):

Aggiungete un elenco delle festività in un foglio di lavoro

Anche in questo caso apriremo Formattazione condizionale > Nuova regola. Nel caso delle festività, utilizzeremo le funzioni CONFRONTA oppure CONTA.SE:

ATTENZIONE
Se avete scelto un colore diverso per le festività, è necessario spostare la regola che le riguarda in cima alla lista in Formattazione condizionale > Gestisci regole …

L’immagine seguente mostra il risultato in Excel 2013:

Formule di formattazione condizionale per evidenziare le festivita

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.

  1. 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
  2. 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.
  3. 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.

Una riga è evidenziata quando un testo in colonna C viene modificato in una data

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.

LEGGI:  #Excel - Come contare e sommare le celle in base al loro colore

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.

Trovare il valore numerico di una 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.

Evidenziare ogni riga che abbia una certa data in una determinata colonna

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.

Formule di Excel per evidenziare le date uguali minori o maggiori di oggi

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.

Regola di formattazione condizionale per evidenziare le celle vuote della colonna Fattura quando la data di consegna è pari o maggiore di oggi

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).

Calcolare la data di completamento basata su data inizio e durata tenendo conto dei weekend

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.
LEGGI:  #Excel - Come calcolare il numero di un giorno nell'anno [Quick Tip]

Regole di formattazione condizionale per evidenziare scadenze e 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 occorse entro un certo intervallo

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.

Formule per evidenziare date future entro un certo intervallo

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.

INFO
Se vi capita di dimenticare questa formula complessa, è possibile utilizzare questa un po’ più semplice: =365-DATA.DIFF($A2; OGGI(), “yd”). Produce esattamente gli stessi risultati, basta ricordarsi di sostituire 365 con 366 negli anni bisestili 🙂

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.

Formattazione condizionale basata su quanti giorni mancano ad un evento

Speriamo che, almeno uno delle formattazioni condizionali di Excel per le date discusse in questo articolo vi sia tornata utile. Grazie per la lettura!

Copyright © 2014-2017  Office Academy. Tutti i diritti riservati.
Vai alla barra degli strumenti