Excel lagrer dato- og klokkeslettverdier som serienumre i bakenden.
Dette betyr at mens du kan se en dato som "10. januar2021-2022" eller "01.10.2020" i en celle, lagrer Excel det i bakenden, som et tall. Dette er nyttig ettersom det også lar brukerne enkelt legge til/trekke fra dato og klokkeslett i Excel.
I Microsoft Excel for Windows lagres "1. januar 1900" som 1, "2. januar 1900" lagres som 2, og så videre.
Nedenfor har begge kolonnene de samme tallene, men kolonne A viser tall og kolonne B viser datoen som er representert med det tallet.
Dette er også grunnen til at du noen ganger kan forvente en dato i en celle, men ender med å se et nummer. Jeg ser dette skje hele tiden når jeg laster ned data fra databaser eller nettbaserte verktøy.
Det skjer når celleformatet er satt til å vise et tall som et tall i stedet for en dato.
Så hvordan kan vi konverter disse serienumrene til datoer?
Det er det denne opplæringen handler om.
I denne opplæringen vil jeg vise deg to veldig enkle måter å konvertere serienumre til datoer i Excel
Merk: Excel for Windows bruker 1900 -datasystemet (som betyr at 1 ville representere 01. januar 1900, mens Excel for Mac bruker datosystemet 1904 (som menn som 1 ville representere 01. januar 1904 i Excel i Mac)Så la oss komme i gang!
Konverter serienumre til datoer ved hjelp av tallformatering
Den enkleste måten å konvertere et dato serienummer til en dato er ved å endre formateringen av cellene som har disse tallene.
Du kan finne noen av de vanlige datoformatene i kategorien Hjem på båndet
La meg vise deg hvordan du gjør dette.
Bruke de innebygde alternativene for datoformat i båndet
Anta at du har et datasett som vist nedenfor, og du vil konvertere alle disse tallene i kolonne A til den tilsvarende datoen det representerer.
Nedenfor er trinnene for å gjøre dette:
- Velg cellene som har nummeret du vil konvertere til en dato
- Klikk på "Hjem" -fanen
- I gruppen "Nummer" klikker du på rullegardinmenyen Nummerformatering
- I rullegardinmenyen velger du alternativet "Lang dato" eller Kort dato (basert på hvilket format du vil at disse tallene skal være i)
Det er det!
Trinnene ovenfor vil konvertere tallene til det valgte datoformatet.
Trinnene ovenfor har ikke endret verdien i cellen, bare måten den vises på.
Vær oppmerksom på at Excel henter den korte datformateringen basert på systemets regionale innstilling. For eksempel, hvis du er i USA, ville datoformatet være MM/DD/ÅÅÅÅ, og hvis du er i Storbritannia, ville datoformatet være DD/MM/ÅÅÅÅ.
Selv om dette er en rask metode for å konvertere serienumre til datoer, har det et par begrensninger:
- Det er bare to datoformater - kort dato og lang dato (og det også i et bestemt format). Så hvis du bare vil få måneden og året og ikke dagen, vil du ikke kunne gjøre det ved å bruke disse alternativene.
- Du kan ikke vise dato og klokkeslett ved å bruke formateringsalternativene i rullegardinmenyen. Du kan enten velge å vise datoen eller klokkeslettet, men ikke begge deler.
Så hvis du trenger mer fleksibilitet i måten du vil vise datoer i Excel på, må du bruke alternativet Egendefinert nummerformatering (dekket neste i denne opplæringen).
Noen ganger når du konverterer et tall til en dato i Excel, kan du i stedet for datoen se noen hash -tegn (noe som #####). dette skjer når kolonnebredden ikke er nok til å romme hele datoen. I et slikt tilfelle er det bare å øke bredden på kolonnen.Opprette et egendefinert datoformat ved hjelp av dialogboks for tallformatering
Anta at du har et datasett som vist nedenfor, og du vil konvertere serienumrene i kolonne A til datoer i følgende format - 01.01.2020
Vær oppmerksom på at dette alternativet ikke er tilgjengelig som standard i båndmetoden som vi dekket tidligere.
Nedenfor er trinnene for å gjøre dette:
- Velg cellene som har nummeret du vil konvertere til en dato
- Klikk på "Hjem" -fanen
- I Numbers -gruppen klikker du på ikonet for startboksen i dialogboksen (det er en liten vippet pil nederst til høyre i gruppen)
- I dialogboksen "Formater celler" som åpnes, må du kontrollere at "Nummer" -fanen er valgt
- I alternativene ‘Kategori’ til venstre, velg ‘Dato’
- Velg ønsket formatering fra "Type" -boksen.
- Klikk OK
Trinnene ovenfor vil konvertere tallene til det valgte datoformatet.
Som du kan se, er det flere alternativer for datoformatering i dette tilfellet (sammenlignet med den korte og lange datoen vi fikk med båndet).
Og hvis du ikke finner formatet du leter etter, kan du også lage ditt eget datoformat.
La oss for eksempel si at jeg vil vise datoen i følgende format - 01/01/2020 (som ikke allerede er et alternativ i dialogboksen formatceller).
Slik kan jeg gjøre dette:
- Velg cellene som har tallene du vil konvertere til en dato
- Klikk på kategorien Hjem
- I Numbers -gruppen klikker du på ikonet for dialogboksen (det er en liten vippepil nederst til høyre i gruppen)
- I dialogboksen "Formater celler" som åpnes, må du kontrollere at "Nummer" -fanen er valgt
- I alternativene ‘Kategori’ til venstre, velg ‘Tilpasset’
- Skriv inn i feltet til høyre
mm/dd/åååå
- Klikk OK
Trinnene ovenfor vil endre alle tallene til det angitte tallformatet.
Med tilpasset format får du full kontroll, og det lar deg vise datoen i hvilket format du vil. Du kan også lage et format der det viser dato og klokkeslett i samme celle.
For eksempel, i samme eksempel, hvis du vil vise dato så vel som tid, kan du bruke formatet nedenfor:
mm/dd/åååå hh: mm AM/PM
Nedenfor er tabellen som viser datoformatkodene du kan bruke:
| Datoformateringskode | Hvordan den formaterer datoen |
| m | Viser måneden som et tall fra 1-12 |
| mm | Vis månedene som tosifrede tall fra 01-12 |
| mmm | Viser måneden som tre bokstaver som i januar-desember |
| mmmm | Viser måneders fulle navn som i januar-desember |
| mmmmm | Viser månedsnavnet første alfabetet som i J-D |
| d | Viser dager som 1-31 |
| dd | Viser dager som 01-31 |
| ddd | Viser dager som søn-lør |
| dddd | Viser dager som søndag-lørdag |
| åå | Viser år som 00-99 |
| åååå | Viser år som 1900-9999 |
Konverter serienumre til datoer ved hjelp av TEKST -formel
Metodene som dekkes ovenfor fungerer ved å endre formateringen av cellene som har tallene.
Men i noen tilfeller kan det hende du ikke kan bruke disse metodene.
For eksempel, nedenfor har jeg et datasett der tallene har en ledende apostrof - som konverterer disse tallene til tekst.
Hvis du prøver å bruke de innebygde alternativene til å endre formateringen av cellene med dette datasettet, ville det ikke gjøre noe. Dette er fordi Excel ikke anser disse som tall, og siden datoer for tall, nekter Excel ditt ønske om å formatere disse.
I et slikt tilfelle kan du enten konvertere denne teksten til tall og deretter bruke formateringsmetodene ovenfor, eller bruke en annen teknikk for å konvertere serienumre til datoer - ved hjelp av TEKST -funksjonen.
TEKST -funksjonen tar to argumenter - tallet som må formateres og formatkoden.
Anta at du har et datasett som vist nedenfor, og du vil konvertere alle tallene og kolonnen A til datoer.
Under formelen som kan gjøre det:
= TEKST (A2, "MM/DD/ÅÅÅÅ")
Vær oppmerksom på at i formelen har jeg angitt datoformateringen til å være i MM/DD/ÅÅÅÅ -formatet. Hvis du trenger å bruke annen formatering, kan du angi koden for den som det andre argumentet i TEKST -formelen.
Du kan også bruke denne formelen til å vise dato så vel som klokkeslett.
For eksempel, hvis du vil at det endelige resultatet skal være i formatet - 01/01/2020 12:00, bruk formelen nedenfor:
= TEKST (A2, "mm/dd/åååå hh: mm AM/PM")
I formelen ovenfor har jeg også lagt til tidsformatet, så hvis det er desimaler i tallene, vil det bli vist som tiden i timer og minutter.
Hvis du bare vil ha datoen (og ikke den underliggende formelen), konverter formlene til statiske verdier ved å bruke lim inn spesial.
En stor fordel med å bruke TEKST -funksjonen til å konvertere serienumre til datoer er at du kan kombinere TEKST -formelresultatet med andre formler. For eksempel kan du kombinere den resulterende datoen med teksten og vise noe som - Forfallsdatoen er 01.01.2020 eller Fristen er 01-Jan-2020
Merk: Husk datoer og klokkeslett er lagret som tall, der hvert heltall ville representere en hel dag og desimaldelen ville representere den store delen av dagen. Så 1 ville bety 1 hel dag og 0,5 ville bety en halv dag eller 12 timer.
Så dette er to enkle måter du kan bruke til å konvertere serienumre til datoer i Microsoft Excel.
Jeg håper du synes denne opplæringen var nyttig!
