Excel lagrar datum- och tidsvärden som serienummer i bakänden.
Det betyder att medan du kanske ser ett datum som "10 januari2021-2022" eller "01/10/2020" i en cell, lagrar Excel det i backend, som ett nummer. Detta är användbart eftersom det också tillåter användare att enkelt lägga till/subtrahera datum och tid i Excel.
I Microsoft Excel för Windows lagras "01 Jan 1900" som 1, "02 Jan 1900" lagras som 2, och så vidare.
Nedan har båda kolumnerna samma nummer, men kolumn A visar siffror och kolumn B visar datumet som representeras av det numret.
Detta är också anledningen till att du ibland kan förvänta dig ett datum i en cell men slutligen se ett nummer. Jag ser detta hända hela tiden när jag laddar ner data från databaser eller webbaserade verktyg.
Det händer när cellformatet är inställt för att visa ett nummer som ett nummer istället för ett datum.
Så hur kan vi konvertera dessa serienummer till datum?
Det är vad denna handledning handlar om.
I denna handledning skulle jag visa dig två riktigt enkla sätt att konvertera serienummer till datum i Excel
Obs: Excel för Windows använder datum 1900 -systemet (vilket innebär att 1 skulle representera 01 januari 1900, medan Excel för Mac använder datumsystemet 1904 (vilket män som 1 skulle representera 01 januari 1904 i Excel i Mac)Så låt oss komma igång!
Konvertera serienummer till datum med hjälp av nummerformatering
Det enklaste sättet att konvertera ett datum serienummer till ett datum är genom att ändra formateringen av cellerna som har dessa nummer.
Du kan hitta några av de vanliga datumformaten på fliken Start i menyfliksområdet
Låt mig visa dig hur du gör detta.
Använda de inbyggda datumformatalternativen i menyfliksområdet
Antag att du har en datauppsättning som visas nedan, och du vill konvertera alla dessa nummer i kolumn A till motsvarande datum som den representerar.
Nedan följer stegen för att göra detta:
- Markera cellerna som har det nummer som du vill konvertera till ett datum
- Klicka på fliken "Hem"
- Klicka på rullgardinsmenyn Nummerformat i gruppen "Nummer"
- I rullgardinsmenyn väljer du alternativet "Långt datum" eller Kort datum (baserat på vilket format du vill att dessa nummer ska vara i)
Det är allt!
Stegen ovan skulle konvertera siffrorna till det valda datumformatet.
Stegen ovan har inte ändrat värdet i cellen, bara hur det visas.
Observera att Excel hämtar kortdatumformateringen baserat på systemets regionala inställning. Om du till exempel är i USA är datumformatet MM/DD/ÅÅÅÅ, och om du är i Storbritannien är datumformatet DD/MM/ÅÅÅÅ.
Även om detta är en snabb metod för att konvertera serienummer till datum, har det ett par begränsningar:
- Det finns bara två datumformat - kort datum och långt datum (och det också i ett specifikt format). Så om du bara vill få månaden och året och inte dagen, kommer du inte att kunna göra det med dessa alternativ.
- Du kan inte visa datum och tid med hjälp av formateringsalternativen i rullgardinsmenyn. Du kan antingen välja att visa datum eller tid men inte båda.
Så om du behöver mer flexibilitet i hur du vill visa datum i Excel måste du använda alternativet Anpassad nummerformatering (täcks nästa i den här självstudien).
Ibland när du konverterar ett nummer till ett datum i Excel kan du istället för datumet se några hashtecken (ungefär #####). detta händer när kolumnbredden inte räcker för att rymma hela datumet. I så fall ökar du bara kolumnens bredd.Skapa ett anpassat datumformat med hjälp av nummerformateringsdialogrutan
Antag att du har en datamängd som visas nedan och du vill konvertera serienumren i kolumn A till datum i följande format - 01.01.2020
Observera att det här alternativet inte är tillgängligt som standard i bandmetoden som vi täckte tidigare.
Nedan följer stegen för att göra detta:
- Markera cellerna som har det nummer som du vill konvertera till ett datum
- Klicka på fliken "Hem"
- I gruppen Numbers klickar du på dialogrutans startprogram (det är en liten lutad pil längst ned till höger i gruppen)
- I dialogrutan "Formatera celler" som öppnas, se till att fliken "Nummer" är vald
- I alternativen "Kategori" till vänster väljer du "Datum"
- Välj önskad formatering från rutan "Typ".
- Klicka på OK
Stegen ovan skulle konvertera siffrorna till det valda datumformatet.
Som du kan se finns det fler datumformateringsalternativ i det här fallet (jämfört med det korta och långa datumet vi fick med menyfliksområdet).
Och om du inte hittar det format du letar efter kan du också skapa ditt eget datumformat.
Låt oss till exempel säga att jag vill visa datumet i följande format - 01/01/2020 (vilket inte redan är ett alternativ i dialogrutan formatceller).
Så här gör jag:
- Markera cellerna som har siffrorna som du vill konvertera till ett datum
- Klicka på fliken Hem
- I Numbers -gruppen klickar du på ikonen för dialogrutans startprogram (det är en liten lutningspil längst ned till höger i gruppen)
- I dialogrutan "Formatera celler" som öppnas, se till att fliken "Nummer" är vald
- I alternativen "Kategori" till vänster väljer du "Anpassad"
- Ange i fältet till höger
mm/dd/åååå
- Klicka på OK
Stegen ovan skulle ändra alla siffror till det angivna nummerformatet.
Med anpassat format får du full kontroll och det låter dig visa datumet i vilket format du vill. Du kan också skapa ett format där det visar datum och tid i samma cell.
Till exempel, i samma exempel, om du vill visa datum såväl som tid, kan du använda nedanstående format:
mm/dd/åååå hh: mm AM/PM
Nedan visas tabellen som visar datumformatkoder du kan använda:
| Datumformateringskod | Hur det formaterar datumet |
| m | Visar månaden som ett tal från 1-12 |
| mm | Visa månaderna som tvåsiffriga nummer från 01-12 |
| mmm | Visar månaden som tre bokstäver som i jan-dec |
| Mmmm | Visar månadernas fullständiga namn som i januari-december |
| mmmmm | Visar månadsnamnet första alfabetet som i J-D |
| d | Visar dagar som 1-31 |
| dd | Visar dagar som 01-31 |
| ddd | Visar dagar som sön-lör |
| dddd | Visar dagar som söndag-lördag |
| åå | Visar år som 00-99 |
| åååå | Visar år 1900-9999 |
Konvertera serienummer till datum med hjälp av TEXT Formula
Metoderna som behandlas ovan fungerar genom att ändra formateringen av cellerna som har siffrorna.
Men i vissa fall kanske du inte kan använda dessa metoder.
Till exempel, nedan har jag en datamängd där siffrorna har en ledande apostrof - som omvandlar dessa siffror till text.
Om du försöker använda de inbyggda alternativen för att ändra cellernas formatering med den här datamängden skulle det inte göra någonting. Detta beror på att Excel inte betraktar dessa som siffror, och eftersom datum för siffror vägrar Excel din önskan att formatera dessa.
I ett sådant fall kan du antingen konvertera denna text till siffror och sedan använda ovanstående formateringsmetoder, eller använda en annan teknik för att konvertera serienummer till datum - med funktionen TEXT.
TEXT -funktionen tar två argument - numret som måste formateras och formatkoden.
Anta att du har en datauppsättning som visas nedan, och du vill konvertera alla siffror och kolumn A till datum.
Under formeln som kan göra det:
= TEXT (A2, "MM/DD/ÅÅÅÅ")
Observera att i formeln har jag angett datumformateringen i formatet MM/DD/ÅÅÅÅ. Om du behöver använda någon annan formatering kan du ange koden för den som det andra argumentet i TEXT -formeln.
Du kan också använda denna formel för att visa både datum och tid.
Om du till exempel vill att det slutliga resultatet ska vara i formatet - 01/01/2020 12:00, använd följande formel:
= TEXT (A2, "mm/dd/åååå hh: mm AM/PM")
I ovanstående formel har jag också lagt till tidsformatet, så om det finns decimaler i siffrorna visas det som tiden i timmar och minuter.
Om du bara vill ha datumet (och inte den underliggande formeln), konvertera formlerna till statiska värden med hjälp av klistra in special.
En stor fördel med att använda TEXT -funktionen för att konvertera serienummer till datum är att du kan kombinera TEXT -formelresultatet med andra formler. Till exempel kan du kombinera det resulterande datumet med texten och visa något som - Förfallodagen är 01-jan-2020 eller Deadline är 01-jan-2020
Obs: Kom ihåg datum och tid lagras som nummer, där varje heltal skulle representera en hel dag och decimaldelen skulle representera den stora delen av dagen. Så 1 skulle betyda en hel dag och 0,5 skulle betyda en halv dag eller 12 timmar.
Så det här är två enkla sätt du kan använda för att konvertera serienummer till datum i Microsoft Excel.
Jag hoppas att du tyckte att denna handledning var användbar!
