Insieme con i numeri, le date e gli orari sono i tipi di dati più comuni che gli utenti usano in Excel. Tuttavia, esse possono generare molta confusione, in primo luogo, perché la stessa data può essere visualizzata in Excel in diversi modi, e in secondo luogo, perché Excel memorizza sempre internamente date nello stesso formato indipendentemente da come avete formattato una data in una determinata cella.
Conoscere i formati data di Excel un po’ più in profondità può aiutare a risparmiare un sacco di tempo. E questo è esattamente lo scopo del nostro tutorial per lavorare con date in Excel.
Il formato data di Excel
Prima di poter usufruire delle potenti funzionalità delle date in Excel, è necessario capire come Microsoft Excel memorizzi date e orari, perché questa è la principale fonte di confusione. Mentre ci si aspetterebbe che Excel ricordasse il giorno, il mese e l’anno di una data, non è così che funziona …
Excel memorizza le date come numeri sequenziali ed è solo la formattazione di una cella che fa sì che il numero venga visualizzato come una data, un’ora, o come una data e un’ora insieme.
Le date in Excel
Tutte le date sono memorizzate come numeri interi che rappresentano il numero di giorni dal 1° gennaio 1900, che viene memorizzato come numero 1, al 31 dicembre 9999 memorizzato come 2.958.465.
In questo sistema:
- 2 è il 2-gen-1900
- 3 è il 3-gen-1900
- 42370 è 1-gen-2016 (perché è 42,370 giorni dopo il 1 gennaio 1900)
Gli orari in Excel
Gli orari sono memorizzati in Excel come decimali, tra il 0,0 e 0,99999, che rappresentano una parte del giorno, dove 0,0 è 00:00:00 e 0,99999 è 23:59:59.
Per esempio:
- 0,25 sono le 06:00
- 0,5 sono le 12:00
- 0,541655093 sono le 12:59:59
Date e orari in Excel
Excel memorizza date e orari come numeri decimali costituiti da un numero intero, che rappresenta la data, e una parte decimale che rappresenta l’orario.
Per esempio:
- 1,25 è il 1° gennaio 1900 alle ore 06:00
- 42370,5 è il 1° gennaio 2016 alle ore 12:00
Come convertire una data in un numero
Se volete sapere quale numero sequenziale rappresenti una certa data o ora visualizzate in una cella, è possibile farlo in due modi.
La finestra di dialogo Formato celle
Selezionate una cella con una data, premete Ctrl + 1 per aprire la finestra di dialogo Formato celle, e passate alla scheda Numero.
Se volete solo conoscere il numero seriale che si nasconde dietro alla data, senza in realtà convertire la data in numero, cliccate su Generale sotto Categoria e annotatevi il numero che vedete sotto Esempio. Poi fate clic su Annulla per chiudere la finestra. Se volete sostituire la data con il relativo numero, allora fate clic su OK.
Le funzioni DATA.VALORE e ORARIO.VALORE di Excel
Utilizzate la funzione DATA.VALORE() per convertire una data in un numero di serie, per esempio:
=DATA.VALORE("16/9/2016")
UtilizzaTe la funzione ORARIO.VALORE() per ottenere il numero decimale che rappresenta l’orario, per esempio:
=ORARIO.VALORE("6:00 AM")
Per conoscere il numero corrispondente ad una data e ad un orario insieme, concatenate queste due funzioni nel modo seguente:
=DATA.VALORE("16/9/2016") & ORARIO.VALORE("6:00 AM")
Se immettete una data precedente in un foglio, ad esempio 31/12/1899, verrà interpretato come valore di testo piuttosto che una data, il che significa che con essa non sarà possibile eseguire i soliti calcoli matematici. Per provare, potete digitare la formula =DATA.VALORE(“31/12/1899”) in una cella: otterrete il valore d’errore #VALORE!.
I formati di data predefiniti in Excel
Quando si lavora con le date in Excel, i formati di data breve e estesa vengono recuperati dalle impostazioni internazionali di Windows. Questi formati di default sono contrassegnati da un asterisco (*) nella finestra di dialogo Formato celle:
I formati di data e ora di default nella casella Formato celle cambiano al cambiare delle impostazioni di data e ora del Pannello di controllo, il che ci porta dritti dritti alla sezione successiva.
Come cambiare i formati predefiniti di data e ora in Excel
Se volete impostare un diverso formato predefinito per le date e gli orari sul computer, ad esempio, da Italiano (Italia) a Inglese (Stati Uniti d’America), andate nel pannello di controllo e cliccate su Area geografica.
Nella scheda Formati, scegliete la regione in Formato, e quindi impostate la formattazione di data e ora facendo clic sulla freccia accanto al formato che volete modificare e selezionando quella desiderata dall’elenco a discesa:
Se non siete soddisfatti da qualsiasi formato data e ora disponibile nella scheda Formati, fate clic sul pulsante Impostazioni aggiuntive nella parte in basso a destra della finestra di dialogo Area geografica. Si aprirà la finestra di dialogo Personalizza formato, dove, passando alla scheda Data, potrete inserire un vostro formato personalizzato di data breve e di data estesa.
Come applicare rapidamente la formattazione data e ora di default in Excel
Microsoft Excel ha due formati predefiniti per le date, breve ed estesa, come spiegato in I formati di data predefiniti in Excel.
Per modificare rapidamente il formato della data per applicare la formattazione predefinita, effettuate le seguenti operazioni:
- Selezionate le date che volete formattare
- Nella scheda Home, nel gruppo Numeri, fare clic sulla piccola freccia accanto alla casella Formato numero, e selezionate il formato desiderato: data breve (indicata come “Data in cifre“), Data estesa oppure Ora.
Se volete più opzioni di formattazione della data, è possibile selezionare “Altri formati numeri…” dall’elenco a discesa o fare clic nella piccola freccetta in basso a destra nel gruppo Numeri. Questo aprirà la familiare finestra di dialogo Formato celle dalla quale si può modificare il formato della data.
Come cambiare il formato della data in Excel
In Microsoft Excel, le date possono essere visualizzate in una miriade di modi. Quando si tratta di cambiare il formato della data di una cella o di un intervallo di celle, il modo più semplice è quello di aprire la finestra di dialogo Formato celle e scegliere uno dei formati predefiniti.
- Selezionate le date il cui formato volete cambiare, oppure le celle vuote in cui desiderate inserire date
- Premere Ctrl + 1 per aprire la finestra di dialogo Formato celle. In alternativa, è possibile fare clic destro sulle celle selezionate e scegliere Formato celle… dal menu contestuale
- Nella finestra Formato celle, passate alla scheda Numero e selezionate Data nell’elenco Categoria
- Sotto Tipo, selezionate un formato di data che vi possa andare bene. Una volta fatto questo, la casella Esempio visualizzerà l’anteprima di formato con la prima data nei dati selezionati.
- Se siete soddisfatti dall’anteprima, fate clic sul pulsante OK per salvare il cambio di formato e chiudere la finestra
Se il formato data non viene modificato, molto probabilmente le vostre date sono formattate come testo e dovete prima convertirle in data.
Come convertire il formato della data ad altre impostazioni locali
Quando avete un file pieno di date estere, molto probabilmente vorrete cambiare il loro formato in quello utilizzato nella vostra parte del mondo. Diciamo che vogliate convertire un formato data americano (mese/giorno/anno) in un formato in stile europeo (giorno/mese/anno).
Il modo più semplice per cambiare il formato della data in Excel in base a come un’altra lingua visualizza le date è il seguente:
- Selezionate la colonna di date che volete convertire ad altre impostazioni locali
- Premete Ctrl + 1 per aprire la finestra di dialogo Formato celle
- Selezionate la lingua desiderata in Impostazioni locali (località) e fate clic su OK per salvare le modifiche
Creare un formato di data personalizzato in Excel
Se nessuno dei formati di data predefiniti di Excel è adatto alle vostre esigenze, siete liberi di crearvene uno vostro.
- In un foglio di Excel, selezionate le celle da formattare
- Premete Ctrl + 1 per aprire la finestra di dialogo Formato celle
- Nella scheda Numero, selezionate Personalizzato dall’elenco Categoria e digitate il formato di data desiderato nella casella Tipo
Nell’impostare un formato di data personalizzata in Excel, è possibile utilizzare i seguenti codici:
| Codice | Descrizione | Esempio (5 Settembre 2016) |
| m | Il numero del mese senza lo zero iniziale | 9 |
| mm | Il numero del mese con lo zero iniziale | 09 |
| mmm | Il nome del mese, forma breve | set |
| mmmm | Il nome del mese, forma estesa | settembre |
| mmmmm | La prima lettera del mese | s |
| g | il numero del giorno senza lo zero iniziale | 5 |
| gg | Il numero del giorno con lo zero iniziale | 05 |
| ggg | Il giorno della settimana, forma breve | lun |
| gggg | Il giorno della settimana, forma estesa | lunedì |
| aa | Anno (ultime 2 cifre) | 16 |
| aaaa | Anno (4 cifre) | 2016 |
Nell’impostare un formato ora personalizzato in Excel, è possibile utilizzare i seguenti codici:
| Codice | Descrizione | Visualizzato come |
| h | Ore senza lo zero iniziale | 0-23 |
| hh | Ore con lo zero iniziale | 00-23 |
| m | Minuti senza lo zero iniziale | 0-59 |
| mm | Minuti con lo zero iniziale | 00-59 |
| s | Secondi senza lo zero iniziale | 0-59 |
| ss | Secondi con lo zero iniziale | 00-59 |
| AM/PM | Periodo del giorno (se omesso viene usato il formato 24-ore) |
AM o PM |
Come creare un formato di data personalizzato per un’altra impostazione locale
Se desiderate visualizzare le date in un’altra lingua, e volete creare un formato personalizzato, occorre precedere i codici data con il codice locale corrispondente. Il codice locale dovrebbe essere racchiuso tra parentesi quadre [] e preceduto dal simbolo del dollaro ($) e un trattino (-). Ecco alcuni esempi:
- [$-409] – Inglese, Stati Uniti
- [$-1009] – Inglese, Canada
- [$-407] – Tedesco, Germania
- [$-807] – Tedesco, Svizzera
- [$-040c] – Francese, Francia
- [$-804] – Cinese, China
Potete trovare l’elenco completo dei codici locali in questa pagina.
Ad esempio, questo è il modo di settare un formato data personalizzato per l’impostazione locale cinese:
Il formato data non funziona – Correzioni e soluzioni
Di solito, Microsoft Excel capisce le date molto bene ed è improbabile rimanere bloccati quando si lavora con esse. Se vi capita di avere un problema con un formato di data, vi invitiamo a consultare i seguenti suggerimenti per trovare una soluzione al problema.
Una cella non è sufficientemente larga per mostrare la data per intero
Se vedete una serie di cancelletti (#####) invece delle date, molto probabilmente le celle non sono sufficientemente ampie per mostrare le date per intero.
Soluzione. Fate doppio clic sul bordo destro della colonna per ridimensionarla ed adattarla automaticamente alle date. In alternativa, è possibile trascinare il bordo destro per impostare la larghezza della colonna che desiderate.
Numeri negativi sono formattati come date
In tutte le versioni moderne di Excel (2016, 2013, 2010 e 2007) i cancelletti (#####) vengono visualizzati anche quando una cella formattata come data o ora contiene un valore negativo. Di solito si tratta di un risultato restituito da una formula, ma può anche accadere quando si digita un valore negativo in una cella e poi si formatta la cella come una data.
Se volete visualizzare numeri negativi come date negative, avete a disposizione due opzioni:
Soluzione 1. Passare al sistema data 1904.
Andate su File > Opzioni > Impostazioni avanzate, scorrete fino alla sezione Durante il calcolo della cartella di lavoro, selezionate la casella di controllo Usa sistema data 1904, e fate clic su OK.
In questo sistema, 0 è il 1-gen-1904; 1 è il 2-gen-1904; e -1 viene visualizzato come una data negativa “-2-gen-1904”.
Naturalmente, tale rappresentazione è molto inusuale e richiede tempo per abituarsi, ma questo è la via giusta da percorrere se volete eseguire calcoli con date precedenti.
Tale soluzione presenta anche alcune controindicazioni. Ne avevamo parlato diffusamente in questo articolo.
Soluzione 2. Utilizzare la funzione TESTO di Excel.
Un altro modo possibile per visualizzare i numeri negativi come date negative in Excel risiede nell’utilizzo della funzione TESTO. Ad esempio, se state sottraendo C1 da B1 e il valore in C1 è maggiore di quello in B1, è possibile utilizzare la seguente formula per produrre il risultato nel formato data:
=TESTO(ASS(B1-C1); "-g-mmm-aaaa")
Consigliamo di modificare l’allineamento delle celle per portarlo a destra, e, naturalmente, all’interno della funzione TESTO potete utilizzare altri formati di data personalizzati.
Le date sono state importate in Excel come valori di testo
Quando si importano i dati in Excel da un file .csv o da qualche altro database esterno, le date sono spesso importate come valori di testo. Possono apparirvi come normali date, ma Excel li percepisce come testo e li tratta di conseguenza.
Soluzione. È possibile convertire “le date testuali” in formato data utilizzando la funzione DATA.VALORE di Excel oppure con il comando “Testo in colonne”.
Questo è tutto su come si formattano le date in Excel. Nella prossima parte della nostra guida, discuteremo dei vari modi per inserire le date e gli orari nei fogli di lavoro di Excel. Grazie per la lettura e alla prossima!
