È comune importare dati da altre fonti in Excel ed è anche comune non ottenere dati formattati correttamente. Ad esempio, la maggior parte delle volte quando riceviamo dati da altre fonti, la data, l'ora e i valori numerici sono in formato testo. Devono essere convertiti nel formato corretto. È facile convertire i numeri in numeri Excel. Ma convertire una stringa in excel datetime potrebbe richiedere un po' di sforzo.
In questo articolo, impareremo come convertire la data e l'ora formattate in testo in data e ora di Excel.
Formula generica
| =DATAVALORE(SINISTRA(stringa_data e ora,10)) + TIMEVALUE(MID(Datetime_string,12,8)) |
Datetime_string: Questo è il riferimento della stringa che contiene la data e l'ora.
Le funzioni principali in questa formula generica sono DATAVALORE e TIMEVALUE. Altre funzioni vengono utilizzate per estrarre la stringa della data e la stringa dell'ora se sono combinate in un'unica stringa. E sì, la cella che contiene questa formula dovrebbe essere formattata nel formato datetime, altrimenti vedrai numeri di serie che potrebbero confonderti.
Vediamo alcuni esempi per chiarire le cose.
Esempio: Converti data e ora importate in Excel Datetime
Qui abbiamo data e ora combinate in un'unica stringa. Dobbiamo estrarre la data e l'ora separatamente e convertirle in excel datetime.
Nota che ho formattato C3 come data in modo che i risultati che ottengo vengano convertiti in date e non visualizzi un numero frazionario. (Segreti di data e ora excel).
Quindi scriviamo la formula:
| =DATAVALORE(SINISTRA(La3,10)) + TIMEVALUE(MID(B3,12,8)) |
E restituisce la data e l'ora che Excel considera come datetime, non come stringa.
Se non modifichi la formattazione della cella, questo è l'aspetto che avrà il tuo risultato.
Questo non è sbagliato. È il numero di serie che rappresenta la data e l'ora. Se non sai come la data e l'ora vengono percepite da Excel, puoi ottenere una chiarezza completa qui.
Come funziona?
Questo è semplice. Utilizziamo tecniche di estrazione per estrarre stringhe di data e ora. Qui abbiamo SINISTRA(B3,10). Questo restituisce 10 caratteri dalla sinistra della stringa in B3. Che è "12/02/2020". Ora questa stringa viene fornita alla funzione DATEVALUE. E DATEVALUE converte la stringa della data nel suo valore. Quindi otteniamo 44167.
Allo stesso modo, otteniamo il valore del tempo usando la funzione MID (per l'estrazione) e la funzione TIMEVALUE.
La funzione MID restituisce 8 caratteri dalla 12a posizione nella stringa. Che è "12:23:00". Questa stringa viene fornita alla funzione TIMEVALUE che restituisce .51597.
Infine aggiungiamo questi numeri. Questo ci dà il numero 44167.51597. Quando convertiamo la formattazione della cella nel formato MM/GG/AAAA HH:MM AM/PM otteniamo la data e l'ora esatta.
Nell'esempio sopra, la data e l'ora erano concatenate insieme come una stringa. Se hai solo la data come stringa senza testo o stringa extra, puoi utilizzare solo la funzione DATEVALUE per ottenere la data convertita in data excel.
| =DATAVALORE(B3) |
Se hai solo tempo come stringa usa il TIMEVALUEfunzione
| =TIMEVALUE(B3) |
Puoi leggere queste funzioni facendo clic sul nome della funzione nella formula.
Quindi sì ragazzi, ecco come convertire una stringa di data e ora in un valore di data e ora di Excel. Spero che questo sia stato utile. Se hai dei dubbi su questo articolo o hai qualsiasi altra domanda relativa a Excel/VBA, chiedimelo nella sezione commenti qui sotto. Sarò felice di aiutarti.
Converti data e ora da GMT a CST : Mentre si lavora su report Excel e dashboard Excel in Microsoft Excel, è necessario convertire la data e l'ora. Inoltre, dobbiamo ottenere la differenza nei tempi in Microsoft Excel.
Come convertire il tempo in decimale in Excel : Dobbiamo convertire le cifre del tempo in decimali usando un foglio di calcolo in Excel 2016. Possiamo manipolare il tempo in Excel 2010, 2013 e 2016 usando la funzione CONVERTI, ORA e MINUTI.
Come convertire la data in testo in Excel : In questo articolo abbiamo imparato come convertire il testo in data, ma come si converte una data excel in testo. Per convertire una data excel in testo abbiamo alcune tecniche.
Conversione del nome del mese in un numero in Microsoft Excel : Quando si tratta di rapporti, i numeri sono migliori del testo. In Excel se vuoi convertire i nomi dei mesi in numeri (1-12). Utilizzando la funzione DATAVALORE e MESE puoi convertire facilmente i mesi in numeri.
Excel converte i secondi decimali in formato ora : Come sappiamo, il tempo in Excel viene trattato come un numero. Ore, Minuti e Secondi vengono trattati come numeri decimali. Quindi, quando abbiamo i secondi come numeri, come li convertiamo nel formato dell'ora? Questo articolo lo ha coperto.
Articoli popolari:
50 scorciatoie di Excel per aumentare la produttività | Diventa più veloce nel tuo compito. Queste 50 scorciatoie ti faranno lavorare ancora più velocemente su Excel.
Come utilizzare la funzione CERCA.VERT di Excel| Questa è una delle funzioni più utilizzate e popolari di Excel che viene utilizzata per cercare valore da diversi intervalli e fogli.
Come usare Excel Funzione CONTA.SE| Conta i valori con le condizioni usando questa fantastica funzione. Non è necessario filtrare i dati per contare valori specifici. La funzione Countif è essenziale per preparare la tua dashboard.
Come utilizzare la funzione SOMMA.SE in Excel | Questa è un'altra funzione essenziale della dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.