In un foglio di Excel ho inserito una query a una pagina web inglese. Una delle colonne si chiama Last report e contiene una data/ora in inglese, scritta così:
12 January 2020 11.00.
A causa del mese in inglese e del punto separatore tra ora e minuti, Excel riconosce la stringa come testo. A me invece serve come data di Excel, ovvero così:
12/01/2020 10:00.
Per prima cosa, in un nuovo foglio ho inerito i mesi inglesi nella colonna A e un numero progressivo da 1 a 12 nella colonna B. Ho dato il nome mesi all’intervallo A1:B:12.
Per ricostruire la data ho usato prima di tutto la funzione =SE.ERRORE(), perché “Last report” può anche essere vuota; al suo interno ho inserito la funzione DATA(ANNO;MESE;GIORNO) a cui ho sommato le ore facendo attenzione a quanto segue:
- I nomi dei mesi hanno lunghezze diverse, per cui STRINGA.ESTRAI() da solo non basta
- I minuti sono sempre a zero
- Le formule di testo restituiscono testo, ma per sommare l’ora alla data ho bisogno dell’ora come numero; inoltre il numero che mi serve non è l’intero ma il suo ventiquattresimo (per Excel un giorno vale 1 e un’ora vale 1/24)
- La cella risultato usa la formattazione personalizzata
gg/mm/aaaa hh:mm
Per ottenere l’ANNO:
SINISTRA(DESTRA([@[Last Report]];10);4)
Per ottenere il MESE:
CERCA.VERT(STRINGA.ESTRAI([@[Last Report]];4;LUNGHEZZA([@[Last Report]])-14);mesi;2;0)
Per ottenere il GIORNO:
SINISTRA([@[Last Report]];2)
Per ottenere le ORE:
NUMERO.VALORE(SINISTRA(DESTRA([@[Last Report]];5);2))/24
La formula finale è questa:
=SE.ERRORE(DATA(SINISTRA(DESTRA([@[Last Report]];10);4);CERCA.VERT(STRINGA.ESTRAI([@[Last Report]];4;LUNGHEZZA([@[Last Report]])-14);mesi;2;0);SINISTRA([@[Last Report]];2))+NUMERO.VALORE(SINISTRA(DESTRA([@[Last Report]];5);2))/24;”-“)