Jeg har stadig problemer med å jobbe med datoer i Excel, jeg må gjøre noe galt, men jeg forstår ikke hva.
Jeg har et regneark, eksportert fra utvekslingsserveren vår, som inneholder en kolonne med datoer. De har kommet ut i amerikansk format selv om jeg er i Storbritannia.
Den aktuelle kolonnen ser slik ut
04/08/2012
04/09/2009
04/01/2010
04/21/2011
04/05/2012
08/30/2009
08/29/2010
08/28/2011
I Excel har jeg markert kolonnen og valgt Format Cells...
. I denne dialogboksen har jeg valgt Date
, valgt English (United States)
som språk og valgt det samsvarende datoformatet fra listen. Jeg trykker OK og prøver å sortere dataene etter denne kolonnen.
I sorteringsdialogen velger jeg denne kolonnen, velger sortering på verdier, men rekkefølgen gir meg bare alternativer for A til Å, ikke eldste til nyeste som jeg hadde forventet.
Dette sorterer igjen datodataene etter de to første sifrene.
Jeg er klar over at jeg kunne omformatere disse dataene til ISO, og da ville A til Z-sorteringen fungere, men jeg burde ikke ha gjort det, jeg mangler åpenbart noe. Hva er det?
EDIT: Jeg rotet opp dusøren, men dette burde ha gått til @ r0berts svar, hans første forslag om tekst til kolonner uten skilletegn og velge ' MDY ' som datatype fungerer. I tillegg, hvis du har et klokkeslett (dvs. 04/21/2015 18:34:22
), må du først kvitte deg med tidsdataene. Men etter det fungerer metoden foreslått av @ r0berts bra.
Problemet: Excel vil ikke gjenkjenne datoer som datoer, selv om du gjennom "Format celler - Nummer - Tilpasset" eksplisitt prøver å fortelle det at dette er datoer med " mm/dd/yyyy
". Som du vet; når Excel har gjenkjent noe som en dato, lagrer den dette videre som et tall - for eksempel "41004" - men vises som dato i henhold til formatet du spesifiserer. For å legge til forvirring kan Excel konvertere bare en del av datoene dine, for eksempel 08/04/2009, men la andre, f.eks. 07/28/2009, ikke konverteres.
Løsning: trinn 1 og deretter 2.
1) Velg datakolonnen. Under Data velger du knappen Tekst til kolonner. På det første skjermbildet lar du radioknappen stå på avgrenset og klikker på Neste. Fjern markeringen i noen av avgrensningsboksene (alle bokser blank; ingen avkrysninger) og klikk Neste. Under kolonneformat velger du Dato og velger MDY i kombinasjonsboksen ved siden av og klikker på Fullfør. Nå har du fått datoverdier (dvs. Excel har gjenkjent verdiene dine som datatype Date
), men formateringen er sannsynligvis fortsatt den lokale datoen, ikke mm/dd/ååååå
som du ønsker.
2) For å få det ønskede amerikanske datoformatet vist riktig må du først velge kolonnen (hvis den ikke er valgt) og deretter under Cell Format - Number velge Date OG velge Locale : English (US). Dette vil gi deg et format som "m/d/å". Deretter kan du velge Egendefinert, og der kan du enten skrive "mm/dd/yyyy" eller velge dette fra listen over egendefinerte strenger.
Alternativ: bruk LibreOffice Calc. Når du limer inn data fra Patricks innlegg, velger du Lim inn spesial (Ctrl+Shift+V) og velger Uformatert tekst. Dette vil åpne dialogboksen "Importer tekst". Tegnsettet forblir Unicode, men for språk velger du engelsk (USA); du bør også merke av i boksen "Detect special numbers". Datoene dine vises umiddelbart i det amerikanske standardformatet og kan sorteres etter dato. Hvis du ønsker det spesielle amerikanske formatet MM/DD/YYYY, må du spesifisere dette én gang gjennom "format celler" - enten før eller etter at du limer inn.
Man kan si - Excel burde ha gjenkjent datoer så snart jeg fortalte det via "celleformat", og jeg kunne ikke vært mer enig. Dessverre er det bare gjennom trinn 1 ovenfra at jeg har klart å få Excel til å gjenkjenne disse tekststrengene som datoer. Åpenbart hvis du gjør dette mye, er det vondt i nakken, og du kan sette sammen en visuell grunnleggende rutine som vil gjøre dette for deg ved å trykke på en knapp.
**Data | Tekst til kolonner
Oppdatering av ledende apostrof etter liming: Du kan se i formellinjen at det er en ledende apostrof i cellen der datoen ikke gjenkjennes. Det betyr at det er en tekststreng i cellen som er formatert som et tall (eller en dato). Du kan si - den ledende apostrofen hindrer regnearket i å gjenkjenne tallet. Du må vite at du må lete i formellinjen etter dette - fordi regnearket bare viser det som ser ut som et venstrejustert tall. For å håndtere dette problemet velger du kolonnen du vil korrigere, velger i menyen Data | Tekst til kolonner
og klikker OK. Noen ganger vil du kunne spesifisere datatypen, men hvis du tidligere har angitt formatet for kolonnen til å være din bestemte datatype - trenger du ikke det. Kommandoen er egentlig ment å dele en tekstkolonne i to eller flere ved hjelp av en skilletegn, men det fungerer som en sjarm for dette problemet også. Jeg har testet det i Libreoffice, men det samme menyelementet finnes også i Excel.
Det ser ut til at Excel ikke gjenkjenner datoene dine som datoer, den gjenkjenner dem som tekst, derfor får du sorteringsalternativene som A til Å. Hvis du gjør det riktig, bør du få noe sånt som dette:
Derfor er det viktig å sørge for at Excel gjenkjenner datoen. Den enkleste måten å gjøre det på, er å bruke snarveien CTRL+SHIFT+3.
Her er hva jeg gjorde med dataene dine. Jeg kopierte det ganske enkelt fra innlegget ditt ovenfor og limte det inn i Excel. Så brukte jeg snarveien ovenfor, og jeg fikk det nødvendige sorteringsalternativet. Sjekk bildet.
Jeg oppretter vanligvis bare en ekstra kolonne for sorterings- eller summeringsformål, så bruk år(a1)"hvis len(måned(a1))=1,),"")"måned(a1)"dag(a1)
.
Det vil gi et yyyymmdd
-resultat som kan sorteres. Bruk av len(a1)
gjør det bare mulig å legge til en ekstra null for månedene 1-9.