Parsing Non-standard datoformater

May 1  by Eliza

Bill står overfor utfordringen med å importere data til Excel som opprinnelig ble opprettet i andre programmer. Problemet er at dataene inneholder massevis av datoer, men de er i et format som Excel ikke forstår. For eksempel kan dato være i formatet 01.15.11 eller 1.15.2011, verken som blir behandlet som en date med Excel. Bill ønsker å vite hvordan å konvertere ikke-standard datoer til et datoformat som Excel forstår.

Hvis datoene er i samme sekvens format som du bruker i dine regionale innstillinger, deretter konvertere et blunk. For eksempel, hvis dine regionale innstillinger bruker datoformatet MDY (måned etterfulgt av dagen etterfulgt av året), og datoen du importerer er i samme format, så kan du bare velge cellene og erstatte perioder med en skråstrek. Når Excel endrer 1.15.2011 til 1/15/2011, analyserer den automatisk resultatet som en dato.

Hvis formatet du importerer ikke samsvarer med dine regionale innstillinger, så du trenger å stokke rundt datoen i samme format. For eksempel, hvis datoen du importerer er 01.10.11 (10 januar 2011), og systemet vil tolke dette som den første oktober 2011, så den enkleste måten er å skille datoen inn enkeltkomponenter, og deretter sette dem tilbake sammen. Følg disse generelle trinnene:

  1. Sett tre tomme kolonner til høyre for datoen kolonnen.
  2. Merk cellene inneholdende de ikke-standard datoer.
  3. Bruke Tekst til kolonner Wizard (Data | Tekst til kolonner) velge avgrensede data og bruke en periode som skilletegn. Når veiviseren er ferdig, vil du ende opp med tre kolonner som inneholder måned, dag og år.
  4. I de resterende blank kolonne, skriv inn en formel for eksempel følgende:

    = DATE (C1, A1, B1)
  5. Kopiere formelen nedover til andre celler ved siden av datoene.
  6. Merk cellene som inneholder formlene du nettopp opprettet, og trykk Ctrl + C.
  7. Bruk Lim inn til å konvertere formlene til resultater. (Velg Verdier alternativet når du bruker Lim inn.)
  8. Slett de tre kolonnene som inneholder de utskilte datoene, og holde kolonnen som inneholder den endelige datoer.

En annen løsning er å bare bruke en makro til å gjøre konverteringen. Følgende er en brukerdefinert funksjon som tar den ikke-standard dato og konverterer den til en riktig formatert datoverdi. Makroen bytter også rundt posisjonen til måned og dag, som gjøres i Tekst til kolonner teknikk.

Public Function Convert_Date (A As String) Som Dato
Dim K Som Long
Dim K1 Som Long
Dim K2 Som Long

K = Len (A)
K1 = InStr (1, A, ".")
K2 = InStr (K1 + 1, A, ".")
Convert_Date = Dateserial (Val (Mid (A, K2 + 1, _
K - K2 + 1)), Val (Mid (A, K1 + 1, K2 - K1)), _
Val (Mid (A, 1, K1 - 1)))
End Function

ExcelTips er din kilde for kostnadseffektiv Microsoft Excel trening. Dette tipset (3191) gjelder for Microsoft Excel 97, 2000, 2002, og 2003. Du kan finne en versjon av dette tipset for Båndet av Excel (Excel 2007 og senere) her: Parsing Non-standard datoformater.