Det er vanlig å importere data fra andre kilder i Excel, og det er også vanlig å ikke få riktig formaterte data. For eksempel, når vi får data fra andre kilder, er dato, klokkeslett og numeriske verdier for det meste i tekstformat. De må konverteres i riktig format. Det er enkelt å konvertere tall til excel -tall. Men å konvertere streng til excel datetime kan trenge litt innsats.
I denne artikkelen lærer vi hvordan du kan konvertere tekstformatert dato og klokkeslett til Excel -dato og -tid.
Generisk formel
| =DATEVALUE(VENSTRE(Datetime_string, 10)) + TIMEVALUE(MIDT(Datetime_string, 12,8)) |
Datetime_string: Dette er referansen til strengen som inneholder dato og klokkeslett.
Hovedfunksjonene i denne generiske formelen er DATEVALUE og TIMEVALUE. Andre funksjoner brukes til å trekke ut datostreng og tidstreng hvis de kombineres til en streng. Og ja, cellen som inneholder denne formelen bør formateres til datetime -format, ellers ser du serienumre som kan forvirre deg.
La oss se noen eksempler for å gjøre ting klart.
Eksempel: Konverter importert dato og klokkeslett til Excel Datetime
Her har vi dato og klokkeslett kombinert til en streng. Vi må trekke ut dato og klokkeslett separat og konvertere dem til excel datetime.
Legg merke til at jeg har formatert C3 som en dato slik at resultatene jeg får blir konvertert til datoer og jeg ikke ser et brøknummer. (Hemmeligheter for excel dato og klokkeslett).
Så la oss skrive formelen:
| =DATEVALUE(VENSTRE(B3,10)) + TIMEVALUE(MIDT(B3,12,8)) |
Og den returnerer dato og klokkeslett som Excel anser som datetime, ikke streng.
Hvis du ikke endrer celleformateringen, ser resultatet slik ut.
Dette er ikke feil. Det er serienummeret som representerer den datoen og klokkeslettet. Hvis du ikke vet hvordan dato og klokkeslett oppfattes av Excel, kan du få fullstendig klarhet her.
Hvordan virker det?
Dette er enkelt. Vi bruker ekstraksjonsteknikker for å trekke ut dato- og klokkeslett. Her har vi VENSTRE (B3,10). Dette returnerer 10 tegn fra venstre på strengen i B3. Som er "12/02/2020". Nå leveres denne strengen til DATEVALUE -funksjonen. Og DATEVALUE konverterer datostrengen til verdien. Derfor får vi 44167.
På samme måte får vi tidsverdi ved å bruke MID -funksjonen (for ekstraksjon) og TIMEVALUE -funksjonen.
MID -funksjonen returnerer 8 tegn fra den 12. posisjonen i strengen. Som er "12:23:00". Denne strengen leveres til TIMEVALUE -funksjonen som returnerer .51597.
Til slutt legger vi til disse tallene. Dette gir oss nummeret 44167.51597. Når vi konverterer celleformateringen til MM/DD/ÅÅÅÅ HH: MM AM/PM -format får vi nøyaktig datotid.
I eksemplet ovenfor hadde vi dato og tid sammenkoblet som en streng. Hvis du bare har dato som streng uten ekstra tekst eller streng, kan du bruke DATEVALUE -funksjonen alene for å få datoen konvertert til excel -dato.
| =DATEVALUE(B3) |
Hvis du bare har tid som en streng, bruker du TIMEVALUEfunksjon
| =TIMEVALUE(B3) |
Du kan lese om disse funksjonene ved å klikke på funksjonsnavnet i formelen.
Så ja gutta, dette er hvordan du konverterer en dato- og klokkeslettstreng til excel -dato -tidsverdi. Jeg håper dette var nyttig. Hvis du er i tvil om denne artikkelen eller har andre spørsmål relatert til Excel/VBA, kan du spørre meg i kommentarfeltet nedenfor. Jeg hjelper deg gjerne.
Konverter dato og klokkeslett fra GMT til CST : Mens vi jobber med Excel -rapporter og Excel -dashbord i Microsoft Excel, må vi konvertere dato og klokkeslett. I tillegg må vi få forskjellen i tidspunkter i Microsoft Excel.
Hvordan konvertere tid til desimal i Excel : Vi må konvertere tidstallene til desimaler ved hjelp av et regneark i Excel 2016. Vi kan manipulere tiden i excel 2010, 2013 og 2016 ved å bruke funksjonen KONVERTER, TIME og MINUTT.
Hvordan konvertere dato til tekst i Excel : I denne artikkelen lærte vi hvordan du konverterer tekst til dato, men hvordan konverterer du en excel -dato til tekst. For å konvertere en excel -dato til tekst har vi noen få teknikker.
Konvertering av månedsnavn til et tall i Microsoft Excel : Når det gjelder rapporter er tall bedre enn tekst. I Excel hvis du vil konvertere månedsnavn til tall (1-12). Ved å bruke funksjonen DATEVALUE og MONTH kan du enkelt konvertere måneder til tall.
Excel konverterer desimalsekunder til tidsformat : Som vi vet blir tid i excel behandlet som tall. Timer, minutter og sekunder behandles som desimaltall. Så når vi har sekunder som tall, hvordan konverterer vi dem til tidsformat? Denne artikkelen fikk det dekket.
Populære artikler:
50 Excel -snarveier for å øke produktiviteten | Bli raskere på oppgaven din. Disse 50 snarveiene får deg til å jobbe enda raskere med Excel.
Slik bruker du Excel VLOOKUP -funksjon| Dette er en av de mest brukte og populære funksjonene til excel som brukes til å slå opp verdi fra forskjellige områder og ark.
Slik bruker du Excel COUNTIF -funksjon| Tell verdier med betingelser ved hjelp av denne fantastiske funksjonen. Du trenger ikke å filtrere dataene dine for å telle spesifikke verdier. Countif -funksjonen er avgjørende for å forberede dashbordet.
Slik bruker du SUMIF -funksjonen i Excel | Dette er en annen viktig funksjon på dashbordet. Dette hjelper deg med å oppsummere verdier på spesifikke forhold.