testo in colonne, restituisce una data invece che orario

Question

salve. necessito un esperto Excel 365 Educational/ Excel 2016 che mi dica come togliere l'automatismo per cui alcuni orari, vengono mostrati nella barra della formula come date (a partire dal 1900!!)

Mi trovo a dover dividere un dato tabellare che deriva da un CSV che ha una colonna data+ora così configurata "201201010030" che indica 2012/01/01 00:30 in due colonne distinte una con la data, e l'altra con l'orario.

Dalla scheda "dati" uso il tool "dividi il testo in colonne" in base alla larghezza e per la data configurata come AMG non c'è problema. Ma il dato orario che trasporta nella colonna affianco mostra, nella barra della formula una data così: 01/01/1900 00:30. E non riesco ad attribuirgli un formato "ora" in nessun modo. Se cambio manualmente, cancellando la data del 1900 dalla barra della formula e lascio solo orario, nel copiare ed incollare tali celle su altri fogli, ritorna la data nella barra della formula! quindi non posso comparare i miei dati orari con altri di altri fogli! avete qualche suggerimento??

Answer 1

Ciao Sabrina,

ecco il file Excel

https://www.dropbox.com/s/alsup17t3t59zsm/Be-Prova%20Microsoft%20Forum.xlsx?dl=0

I campi sono stati creati con la procedura VBA che mi ha postato ed infatti è venuto perfettamente.

Quando però, carico con una query su un altro file vuoto, solo alcune colonne del file, nella colonna contenente le ore ottenuta con VBA,(ma fa lo stesso se la ottengo con la formula ESTRAI) ripropone la questione delle Date accanto all'ora

ti posto il link della query

https://www.dropbox.com/s/x3942vmk5t71wgr/Screenshot%202017-07-21%2015.37.08.png?dl=0

e della tabella creata con la query

https://www.dropbox.com/s/dd1jkw877igpsdp/Be-Prova%20Microsoft%20Forum%20Query.xlsx?dl=0

NB: una volta importata nella query la colonna con le date e l'ora posso riattribuire il Formato cella "ora" e tutto torna apposto. Volevo però evitare questo passaggio ogni volta che devo importare le colonne

Grazie mille :)

Il tuo problema non ha niente da fare con le soluzioni proposte in rispetto della tua domanda originaria e nasce semplicemente dal formato imposto nelle colonne B e D del tuo file, ovvero il formato personalizzato:

 gg/mm/aaaa oo:mm.

A questo proposito, nota che Excel memorizza date e orari come un numero che rappresenta il numero di giorni dal 0-gennaio-1900, più una porzione frazionaria di un giorno di 24 ore: ggggg.tttttt. Questo si chiama una data seriale o una data-ora seriale. Quindi, prendiamo come esempio l'ora 8:30. Impostando un formato oo:mm, si la vedrebbe come

      

Comunque, impostando il formato personalizzato gg/mm/aaaa oo:mm, questo valore sarebbe visualizzato nella forma:

      

Questo perchè il valore comprende 0 (zero) giorni che viene risconosciuto come il 0, gennaio, 1900,

Pertanto, per risolvere  il tuo problema, bastarebbe impostare il formato oo:mm per le colonne di interesse. Allora, come per magia, i tuoi dati si transformano:

===

Regards,

Norman

Answer 2

Ciao maninadily,

Benvenuto alla Community!

salve. necessito un esperto Excel 365 Educational/ Excel 2016 che mi dica come togliere l'automatismo per cui alcuni orari, vengono mostrati nella barra della formula come date (a partire dal 1900!!)

Mi trovo a dover dividere un dato tabellare che deriva da un CSV che ha una colonna data+ora così configurata "201201010030" che indica 2012/01/01 00:30 in due colonne distinte una con la data, e l'altra con l'orario.

Dalla scheda "dati" uso il tool "dividi il testo in colonne" in base alla larghezza e per la data configurata come AMG non c'è problema. Ma il dato orario che trasporta nella colonna affianco mostra, nella barra della formula una data così: 01/01/1900 00:30. E non riesco ad attribuirgli un formato "ora" in nessun modo. Se cambio manualmente, cancellando la data del 1900 dalla barra della formula e lascio solo orario, nel copiare ed incollare tali celle su altri fogli, ritorna la data nella barra della formula! quindi non posso comparare i miei dati orari con altri di altri fogli! avete qualche suggerimento??

Poniamo che i valori di interesse siano nell'intervallo A1:A10.

Nella cella B1, immetti la formula

     =DATA(SINISTRA(A1;4);STRINGA.ESTRAI(A1;5;2);STRINGA.ESTRAI(A1;7;2))

Nella cella C1, immetti la formula

     =ORARIO(STRINGA.ESTRAI(A1;9;2);DESTRA(A1;2);0)

Trascina le due formule in basso quanto necessario

===

Regards,

Norman

Answer 3

Ciao Sabrina,

sarebbe davvero eccellente! Anche così, comunque,mi si è risolto il problema. 

Se poi ci fosse un metodo ancor più veloce (dato che devo ripeterlo per vari fogli excel contenenti a volte anche 175.000 righe ) te ne sarei infinitamente grata. Ma devo precisare che la riga N.1 contiene le intestazioni, e quindi tutto comincia dalla cella A2, ed in VBA non potrei adattarlo da sola ;)

Prova qualcosa del genere

• Alt+F11 per aprire l'editor di VBA
• Alt+IMper inserire un nuovo modulo di codice
• Nel nuovo modulo vuoto, incolla il seguente codice:

'=========>>

Option Explicit

'--------->>

Public Sub Tester()

    Dim WB As Workbook

    Dim SH As Worksheet

    Dim srcRng As Range, destRng As Range

    Dim arrIn As Variant, arrOut() As Variant

    Dim sStr As String

    Dim sYear As String, sMonth As String, sDay As String

    Dim sHour As String, sMinute As String

    Dim LRow As Long, UB As Long

    Dim i As Long, iTime As Long

    Dim CalcMode As Long

    Set WB = ThisWorkbook

    For Each SH In WB.Windows(1).SelectedSheets

        With SH

            LRow = LastRow(SH, .Columns("A:A"))

            Set srcRng = .Range("A2:B" & LRow)

            Set destRng = srcRng

        End With

        arrIn = srcRng.Value

        UB = UBound(arrIn)

        ReDim arrOut(1 To UB, 1 To 2)

        For i = 1 To UB

            sStr = arrIn(i, 1)

            If Len(sStr) = 12 And IsNumeric(sStr) Then

                sYear = Left(sStr, 4)

                sMonth = Mid(sStr, 5, 2)

                sDay = Mid(sStr, 7, 2)

                sHour = Mid(sStr, 9, 2)

                sMinute = Right(sStr, 2)

                arrOut(i, 1) = DateSerial(sYear, sMonth, sDay)

                arrOut(i, 2) = TimeSerial(sHour, sMinute, 0)

            Else

                If IsDate(sStr) Then

                    arrOut(i, 1) = CDate(sStr)

                    arrOut(i, 2) = arrIn(i, 2)

                Else

                    arrOut(i, 1) = sStr

                End If

            End If

        Next i

        On Error GoTo XIT

        With Application

            CalcMode = .Calculation

            .Calculation = xlCalculationManual

            .ScreenUpdating = False

        End With

        With destRng

            .Columns(1).NumberFormat = "dd/mm/yyyy;@"

            .Columns(2).NumberFormat = "hh:mm"

            .Value = arrOut

        End With

        Erase arrOut

    Next SH

    Call MsgBox( _

         Prompt:="Fatto", _

         Buttons:=vbInformation, _

         Title:="REPORT")

XIT:

    With Application

        .Calculation = CalcMode

        .ScreenUpdating = True

    End With

    ActiveSheet.Select

End Sub

'--------->>

Public Function LastRow(SH As Worksheet, _

                        Optional Rng As Range, _

                        Optional minRow As Long = 1)

    If Rng Is Nothing Then

        Set Rng = SH.Cells

    End If

    On Error Resume Next

    LastRow = Rng.Find(What:="*", _

                       after:=Rng.Cells(1), _

                       Lookat:=xlPart, _

                       LookIn:=xlFormulas, _

                       SearchOrder:=xlByRows, _

                       SearchDirection:=xlPrevious, _

                       MatchCase:=False).Row

    On Error GoTo 0

    If LastRow < minRow Then

        LastRow = minRow

    End If

End Function

'<<========= 

• Alt+Q per chiudere l'editor di VBA e tornare a Excel
• Salva il file con l’estensione xlsm
• Seleziona il foglio, o raggruppare i fogli, di interesse
• Alt+F8 per aprire  la finestra di gestione delle macro
• Seleziona Tester | Esegui

===

Regards,

Norman

Answer 4

Ciao maninadily,

Aggiungerei che, se vorresti evitare l'uso di formule e automatizzare la conversione dei valori della colonna A nelle loro costituenti date e ore, potrò postare una semplice procedura VBA.

===

Regards,

Norman

Ciao maninadily,

Aggiungerei che, se vorresti evitare l'uso di formule e automatizzare la conversione dei valori della colonna A nelle loro costituenti date e ore, potrò postare una semplice procedura VBA.

Gent.mo Norman

sarebbe davvero eccellente! Anche così, comunque,mi si è risolto il problema.

Se poi ci fosse un metodo ancor più veloce (dato che devo ripeterlo per vari fogli excel contenenti a volte anche 175.000 righe ) te ne sarei infinitamente grata. Ma devo precisare che la riga N.1 contiene le intestazioni, e quindi tutto comincia dalla cella A2, ed in VBA non potrei adattarlo da sola ;)

Cordiali saluti

Sabrina

===

Regards,

Norman

Answer 5

Ciao maninadily,

Aggiungerei che, se vorresti evitare l'uso di formule e automatizzare la conversione dei valori della colonna A nelle loro costituenti date e ore, potrò postare una semplice procedura VBA.

===

Regards,

Norman