Excel archivia i valori di data e ora come numeri di serie nel back-end.
Ciò significa che mentre potresti vedere una data come "10 gennaio2021-2022" o "01/10/2020" in una cella, nel back-end, Excel la memorizza come numero. Questo è utile in quanto consente anche agli utenti di aggiungere/sottrarre facilmente data e ora in Excel.
In Microsoft Excel per Windows, "01 gennaio 1900" viene archiviato come 1, "02 gennaio 1900" viene archiviato come 2 e così via.
Di seguito, entrambe le colonne hanno gli stessi numeri, ma la colonna A mostra i numeri e la colonna B mostra la data rappresentata da quel numero.
Questo è anche il motivo per cui a volte potresti aspettarti una data in una cella ma finire per vedere un numero. Vedo che questo accade continuamente quando scarico dati da database o strumenti basati sul web.
Succede quando il formato della cella è impostato per mostrare un numero come numero anziché come data.
Allora come possiamo? convertire questi numeri di serie in date?
Ecco di cosa tratta questo tutorial.
In questo tutorial, ti mostrerei due modi davvero semplici per convertire i numeri di serie in date in Excel
Nota: Excel per Windows utilizza il sistema di data 1900 (il che significa che 1 rappresenterebbe il 01 gennaio 1900, mentre Excel per Mac utilizza il sistema di data 1904 (che per gli uomini che 1 rappresenterebbe il 01 gennaio 1904 in Excel in Mac)Quindi iniziamo!
Converti numeri di serie in date utilizzando la formattazione dei numeri
Il modo più semplice per convertire un numero seriale di data in una data consiste nel modificare la formattazione delle celle che contengono questi numeri.
Puoi trovare alcuni dei formati di data comunemente usati nella scheda Home della barra multifunzione
Lascia che ti mostri come farlo.
Utilizzo delle opzioni di formato della data incorporate nella barra multifunzione
Supponiamo di avere un set di dati come mostrato di seguito e di voler convertire tutti questi numeri nella colonna A nella data corrispondente che rappresenta.
Di seguito sono riportati i passaggi per eseguire questa operazione:
- Seleziona le celle che hanno il numero che vuoi convertire in una data
- Fare clic sulla scheda "Home"
- Nel gruppo "Numero", fai clic sul menu a discesa Formattazione numeri
- Nel menu a discesa, seleziona l'opzione "Data lunga" o Data breve (in base al formato in cui desideri che siano presenti questi numeri)
Questo è tutto!
I passaggi precedenti convertiranno i numeri nel formato della data selezionato.
I passaggi precedenti non hanno modificato il valore nella cella, ma solo il modo in cui viene visualizzato.
Nota che Excel raccoglie la formattazione della data breve in base alle impostazioni internazionali del tuo sistema. Ad esempio, se ti trovi negli Stati Uniti, il formato della data sarà MM/GG/AAAA e, se ti trovi nel Regno Unito, il formato della data sarà GG/MM/AAAA.
Sebbene questo sia un metodo rapido per convertire i numeri di serie in date, presenta un paio di limitazioni:
- Esistono solo due formati di data: data breve e data lunga (e anche questo in un formato specifico). Quindi, se vuoi ottenere solo il mese e l'anno e non il giorno, non sarai in grado di farlo usando queste opzioni.
- Non puoi mostrare la data e l'ora utilizzando le opzioni di formattazione nel menu a discesa. Puoi scegliere di visualizzare la data o l'ora ma non entrambe.
Quindi, se hai bisogno di maggiore flessibilità nel modo in cui desideri mostrare le date in Excel, devi utilizzare l'opzione Formattazione numeri personalizzata (trattata di seguito in questo tutorial).
A volte, quando converti un numero in una data in Excel, invece della data potresti vedere alcuni segni di cancelletto (qualcosa come #####). questo accade quando la larghezza della colonna non è sufficiente per contenere l'intera data. In tal caso, basta aumentare la larghezza della colonna.Creazione di un formato data personalizzato utilizzando la finestra di dialogo Formattazione numeri
Supponiamo di avere un set di dati come mostrato di seguito e di voler convertire i numeri di serie nella colonna A in date nel seguente formato - 01.01.2020
Nota che questa opzione non è disponibile per impostazione predefinita nel metodo della barra multifunzione che abbiamo trattato in precedenza.
Di seguito sono riportati i passaggi per eseguire questa operazione:
- Seleziona le celle che hanno il numero che vuoi convertire in una data
- Fare clic sulla scheda "Home"
- Nel gruppo Numbers, fai clic sull'icona di avvio della finestra di dialogo (è una piccola freccia inclinata in basso a destra del gruppo)
- Nella finestra di dialogo "Formato celle" che si apre, assicurati che la scheda "Numero" sia selezionata
- Nelle opzioni "Categoria" a sinistra, seleziona "Data"
- Seleziona la formattazione desiderata dalla casella "Tipo".
- Fare clic su OK
I passaggi precedenti convertiranno i numeri nel formato della data selezionato.
Come puoi vedere, in questo caso ci sono più opzioni di formattazione della data (rispetto alla data breve e lunga che abbiamo ottenuto con il nastro).
E nel caso in cui non trovi il formato che stai cercando, puoi anche creare il tuo formato della data.
Ad esempio, supponiamo di voler mostrare la data nel seguente formato - 01/01/2020 (che non è già un'opzione nella finestra di dialogo Formato celle).
Ecco come posso farlo:
- Seleziona le celle che hanno i numeri che vuoi convertire in una data
- Fare clic sulla scheda Home
- Nel gruppo Numbers, fai clic sull'icona di avvio della finestra di dialogo (è una piccola freccia inclinata in basso a destra del gruppo)
- Nella finestra di dialogo "Formato celle" che si apre, assicurati che la scheda "Numero" sia selezionata
- Nelle opzioni "Categoria" a sinistra, seleziona "Personalizzato"
- Nel campo a destra, inserisci
mm/gg/aaaa
- Fare clic su OK
I passaggi precedenti cambierebbero tutti i numeri nel formato numerico specificato.
Con il formato personalizzato, ottieni il pieno controllo e ti consente di mostrare la data in qualsiasi formato tu voglia. Puoi anche creare un formato in cui mostri la data e l'ora nella stessa cella.
Ad esempio, nello stesso esempio, se vuoi mostrare la data e l'ora, puoi utilizzare il formato seguente:
mm/gg/aaaa hh:mm AM/PM
Di seguito è riportata la tabella che mostra i codici formato data che è possibile utilizzare:
| Codice di formattazione della data | Come formatta la data |
| m | Mostra il mese come un numero da 1-12 |
| mm | Mostra i mesi come numeri a due cifre da 01-12 |
| mmm | Mostra il mese di tre lettere come in gennaio-dicembre |
| mmmm | Mostra il nome completo dei mesi come in gennaio-dicembre |
| mmmmm | Mostra il primo alfabeto del nome del mese come in J-D |
| D | Mostra i giorni come 1-31 |
| dd | Mostra i giorni come 01-31 |
| ddd | Mostra i giorni come domenica-sabato |
| dddd | Mostra i giorni come domenica-sabato |
| yy | Mostra gli anni come 00-99 |
| aaaa | Mostra gli anni come 1900-9999 |
Converti i numeri di serie in date usando la formula TEXT
I metodi descritti sopra funzionano modificando la formattazione delle celle che contengono i numeri.
Ma in alcuni casi, potresti non essere in grado di utilizzare questi metodi.
Ad esempio, di seguito ho un set di dati in cui i numeri hanno un apostrofo iniziale, che converte questi numeri in testo.
Se provi a utilizzare le opzioni integrate per modificare la formattazione delle celle con questo set di dati, non farebbe nulla. Questo perché Excel non li considera come numeri e poiché le date per i numeri, Excel rifiuta il tuo desiderio di formattarli.
In tal caso, puoi convertire questi testi in numeri e quindi utilizzare i metodi di formattazione sopra descritti, oppure utilizzare un'altra tecnica per convertire i numeri di serie in date - utilizzando la funzione TESTO.
La funzione TESTO accetta due argomenti: il numero che deve essere formattato e il codice del formato.
Supponiamo di avere un set di dati come mostrato di seguito e di voler convertire tutti i numeri e la colonna A in date.
Sotto la formula che potrebbe farlo:
=TESTO(A2,"MM/GG/AAAA")
Nota che nella formula ho specificato che la formattazione della data sia nel formato MM/GG/AAAA. Se è necessario utilizzare qualsiasi altra formattazione, è possibile specificarne il codice come secondo argomento della formula TESTO.
Puoi anche usare questa formula per mostrare la data e l'ora.
Ad esempio, se si desidera che il risultato finale sia nel formato - 01/01/2020 12:00 AM, utilizzare la formula seguente:
=TESTO(A2,"mm/gg/aaaa hh:mm AM/PM")
Nella formula sopra, ho aggiunto anche il formato dell'ora, quindi se ci sono parti decimali nei numeri, verrebbe mostrato come il tempo in ore e minuti.
Se vuoi solo la data (e non la formula sottostante), converti le formule in valori statici usando incolla speciale.
Un grande vantaggio dell'utilizzo della funzione TESTO per convertire i numeri seriali in date è che è possibile combinare il risultato della formula TESTO con altre formule. Ad esempio, puoi combinare la data risultante con il testo e mostrare qualcosa come - La data di scadenza è 01-gennaio-2020 o La scadenza è il 01-gen-2020
Nota: ricorda che le date e l'ora sono memorizzate come numeri, dove ogni numero intero rappresenterebbe un giorno intero e la parte decimale rappresenterebbe quella parte del giorno. Quindi 1 significherebbe 1 giorno intero e 0,5 significherebbe mezza giornata o 12 ore.
Quindi questi sono due semplici modi che puoi usare per convertire i numeri di serie in date in Microsoft Excel.
Spero che tu abbia trovato utile questo tutorial!
