Date & Ore ed il metodo Replace. - pagina vista: volte

Ovvero: trasformare in date o ore valori memorizzati come testo.

Può capitare, specialmente se si importano date e/o orari da tabelle poste su pagine web, o da connessioni ad origini che conservano comunque dati in formato stringa (testo), in tabelle su fogli di cartelle di lavoro Excel, di non poter usare questi dati come tali.

Excel infatti riconosce questi dati solo come testo, rendendo impossibile l'utilizzo di procedure che invece si basino su variabili di tipo Date. Potremmo, ad esempio, cercare una determinata data tra le date così inserite, non ottenendo il risultato voluto: noi cerchiamo una data, ma Excel non vede e non considera Data i valori inseriti come testo.

Esiste poi il problema che spesso i dati così importati (o comunque scritti), non rispecchiano la sintassi tradizionale che vuole una data scritta con le barre ( / ) o i trattini ( - ) come separatori ma possono presentarsi con altri separatori, come ad esempio il punto ( . ), come in questo esempio:


Se vogliamo, queste date sono scritte più simili alla sintassi Ora che non alla sintassi Data. Comunque sia, possiamo modificare, usando il metodo Replace, (vedi articolo su questo sito), la sintassi, sostituendo il punto ( . ) con una barra ( / ).

Dobbiamo però considerare due cose:

  1. il valore così modificato, viene ancora visto da Excel come formato Testo, e non come data: per ovviare a ciò, basterebbe impostare il formato cella a Data, usando la proprietà NumberFormat dell'oggetto Range, definendo il formato che vorremo, ad esempio "dd/mm/yy" (giorno/mese/anno a due cifre).

  2. Le cose non vanno automaticamente a posto, esiste un secondo problema: il vba ragiona in inglese, e per lui le date vengono interpretate con il formato inglese, cioè mese/giorno/anno, quindi "mm/dd/yy". Nell'uso di istruzioni vba in cui si voglia impostare un formato cella a data (NumberFormat), relativo ad un valore testo, bisogna considerare che il vba leggendo la stringa di testo, cerca di identificarlo come data, ed usando il sistema inglese, leggerà il valore come mese/giorno/anno; tutto va bene se un valore ha una corrispondenza nei due sistemi, il dato viene interpretato correttamente e passato al foglio dove Excel interviene, trasformando la data letta in inglese, in data scritta in italiano; ma quando non esiste una corrispondenza, come ad esempio nel valore 25/08/03 dove (letto in inglese) il mese 25 non esiste, il vba inverte il mese con il giorno e come tale lo passa ad Excel che ce lo mostrerebbe come 08/25/03, quindi stravolgendolo completamente.

Soluzione. inseriamo un'istruzione condizionale, che leggendo le prime due cifre del valore, se queste due cifre sono maggiori di 12 (numero massimo di mesi in un anno), imposti il formato italiano (dd/mm/yy), altrimenti il formato inglese (mm/dd/yy), ed Excel poi fa l'inversione giusta. Questa una possibile routine:

Sub CambiaFormato()
Dim CL As Object
Dim A As Date
For Each CL In Range("G1:G20")  'intervallo su cui si interviene
If CL <> "" Then                         'si controlla che la cella contenga dati
CL.Replace What:=".", Replacement:="/"     'quindi si sostituisce il punto con la barra
A = CL
iniz = Mid(A, 1, 2)    'con la variabile Variant "iniz" prendiamo le due lettere iniziali lette da sinistra
If iniz > 12 Then      'se iniz è maggiore di 12
CL.NumberFormat = "dd/mm/yy"     'si imposta il formato cella a data in italiano
CL = A
Else
CL.NumberFormat = "mm/dd/yy"     'altrimenti lo impostiamo in inglese, ed Excel lo renderà in italiano
CL = A
End If: End If
Next
End Sub

e questo il risultato:

Ora sarà possibile utilizzare istruzioni con variabili Date sull'intervallo.

Lavorando invece con Ore, e sempre ipotizzando di avere un elenco orari in celle formattate a "testo", le istruzioni differiscono di poco; basterà sostituire il punto ( . ) del separatore con i due punti ( : ) e quindi impostare NumberFormat alla sintassi corrispondente voluta, ad esempio = "hh:mm:ss".

Va sottolineato che nel caso di orari, Excel considera 24 come limite superiore di ore (24 ore in un giorno, anzi per l'esattezza 23:59:59 è il limite consentito, 24 infatti non esiste perchè corrisponderebbe alle ore 00:00:00, cioè all'orario iniziale di un nuovo giorno); nell'esempio della tabella vista all'inizio, il valore 25.08.03, verrà interpretato come 01.08.03 (scomponendo in ore 25 abbiamo: 24 + 1, quindi 00 +1 = 01). Questa la routine:

Sub CambiaFormatoOre()
Dim CL As Object
Dim A As Date              'anche le ore sono variabili di tipo Date/Time e si dichiarano come tipo : Date
For Each CL In Range("D1:D20")
If CL <> "" Then
CL.Replace What:=".", Replacement:=":"
A = CL
CL.NumberFormat = "hh:mm:ss"
CL = A
End If
Next
End Sub

e vedremo quindi:

 origine testo  convertite in ore

Va detto che nel caso di ore e di valori il cui separatore è il punto ( . ) non è necessario usare Replace, ma semplicemente assimilare il valore letto ad un valore Date, ed usare il NumberFormat per impostare il formato, così:

Sub CambiaFormatoOre2()
Dim CL As Object
Dim A As Date
For Each CL In Range("D1:D20")
If CL <> "" Then
A = CL
CL.NumberFormat = "hh:mm:ss"
CL = A
End If
Next
End Sub

Con le istruzioni appena sopra però non saranno consigliati orari superiori a 23:59:59 in quanto la conversione avviene senza aver usato Replace che avvisa Excel che il valore testo va letto come orario (con i due punti, quindi assimilabile a Date), e il valore 25.08.03 diventerebbe 00.00.00.

Esistono casi in cui però si potrebbero usare valori testo da convertire in ore, in cui si faccia uso di orari superiori alle 23.59.59 , come la somma di ore di turni di lavoro, e quindi sia necessario conservare l'effettiva quantità vista come orario intero; è sufficiente modificare il formato da assegnare a NumberFormat, usando la seguente sintassi:

oggetto.NumberFormat = "[h]:mm:ss;@"

 

Buon lavoro.

prelevato sul sito www.ennius.altervista.org