Het is gebruikelijk om gegevens uit andere bronnen in Excel te importeren en het is ook gebruikelijk dat gegevens niet correct zijn opgemaakt. Wanneer we bijvoorbeeld gegevens uit andere bronnen krijgen, zijn de datum, tijd en numerieke waarden meestal in tekstindeling. Ze moeten worden omgezet in het juiste formaat. Het is gemakkelijk om getallen om te zetten in Excel-nummers. Maar het converteren van string naar Excel datetime kost misschien wat moeite.
In dit artikel zullen we leren hoe u datum en tijd met tekstopmaak kunt converteren naar Excel-datum en -tijd.
Generieke formule
| =DATUMWAARDE(LINKS(Datetime_string,10)) + TIJDSWAARDE(MIDDEN(Datetime_string,12,8)) |
Datetime_string: Dit is de referentie van de tekenreeks die de datum en tijd bevat.
De belangrijkste functies in deze generieke formule zijn: DATUMWAARDE en TIJDSWAARDE. Andere functies worden gebruikt om datumstring en tijdstring te extraheren als ze in één string worden gecombineerd. En ja, de cel die deze formule bevat, moet worden opgemaakt in datetime-indeling, anders ziet u serienummers die u in verwarring kunnen brengen.
Laten we enkele voorbeelden bekijken om dingen duidelijk te maken.
Voorbeeld: Geïmporteerde datum en tijd converteren naar Excel Datetime
Hier hebben we datum en tijd gecombineerd in één string. We moeten datum en tijd apart extraheren en converteren naar Excel datetime.
Merk op dat ik C3 heb opgemaakt als een datum, zodat de resultaten die ik krijg worden omgezet in datums en ik geen fractioneel getal zie. (Geheimen van Excel datum en tijd).
Dus laten we de formule schrijven:
| =DATUMWAARDE(LINKS(B3,10)) + TIJDSWAARDE(MIDDEN(B3,12,8)) |
En het retourneert de datum en tijd die Excel beschouwt als datetime, niet als string.
Als u de celopmaak niet wijzigt, ziet uw resultaat er zo uit.
Dit is niet verkeerd. Het is het serienummer dat die datum en tijd vertegenwoordigt. Als u niet weet hoe de datum en tijd door Excel worden waargenomen, kunt u hier volledige duidelijkheid krijgen.
Hoe werkt het?
Dit is eenvoudig. We gebruiken extractietechnieken om datum- en tijdreeksen te extraheren. Hier hebben we LINKS (B3,10). Dit retourneert 10 tekens vanaf de linkerkant van de tekenreeks in B3. Dat is "12/02/2020". Nu wordt deze string geleverd aan de DATEVALUE-functie. En DATEVALUE converteert de datumreeks naar zijn waarde. Daarom krijgen we 44167.
Op dezelfde manier krijgen we de tijdwaarde met behulp van de MID-functie (voor extractie) en de TIMEVALUE-functie.
De MID-functie retourneert 8 tekens vanaf de 12e positie in string. Dat is "12:23:00". Deze tekenreeks wordt geleverd aan de functie TIJDWAARDE die .51597 retourneert.
Als laatste tellen we deze getallen bij elkaar op. Dit geeft ons het nummer 44167.51597. Wanneer we de celopmaak converteren naar het MM/DD/YYYY UU:MM AM/PM-formaat, krijgen we de exacte datetime.
In het bovenstaande voorbeeld hadden we datum en tijd samengevoegd tot een string. Als u alleen datum als tekenreeks heeft zonder extra tekst of tekenreeks, kunt u alleen de functie DATUMWAARDE gebruiken om de datum om te zetten in Excel-datum.
| =DATUMWAARDE(B3) |
Als je gewoon tijd hebt als string, gebruik dan de TIJDSWAARDEfunctie
| =TIJDSWAARDE(B3) |
U kunt over deze functies lezen door op de functienaam in de formule te klikken.
Dus ja jongens, dit is hoe je een datum- en tijdreeks omzet in Excel-datumtijdwaarde. Ik hoop dat dit nuttig was. Als je twijfels hebt over dit artikel of een andere vraag hebt over Excel/VBA, vraag het me dan in de commentaarsectie hieronder. Ik zal u graag helpen.
Datum en tijd converteren van GMT naar CST : Tijdens het werken aan Excel-rapporten en Excel-dashboards in Microsoft Excel, moeten we datum en tijd converteren. Bovendien moeten we het verschil in timing in Microsoft Excel krijgen.
Hoe tijd naar decimalen te converteren in Excel : We moeten de tijdcijfers converteren naar decimalen met behulp van een spreadsheet in Excel 2016. We kunnen de tijd manipuleren in Excel 2010, 2013 en 2016 met de functie CONVERT, HOUR en MINUTE.
Hoe datum naar tekst in Excel te converteren : In dit artikel leerden we hoe je tekst omzet in datum, maar hoe zet je een Excel datum om in tekst. Om een Excel-datum om te zetten in tekst hebben we een aantal technieken.
Maandnaam omzetten in een getal in Microsoft Excel : Als het op rapporten aankomt, zijn cijfers beter dan tekst. In Excel als u maandnamen wilt converteren naar getallen (1-12). Met behulp van de functie DATUMWAARDE en MAAND kunt u eenvoudig maanden omrekenen naar getallen.
Excel converteert decimale seconden naar tijdnotatie : Zoals we weten, wordt tijd in Excel behandeld als getallen. Uren, minuten en seconden worden behandeld als decimale getallen. Dus als we seconden als getallen hebben, hoe zetten we ze dan om in tijdnotatie? Dit artikel heeft het gedekt.
Populaire artikels:
50 Excel-snelkoppelingen om uw productiviteit te verhogen | Word sneller in uw taak. Met deze 50 sneltoetsen werk je nog sneller in Excel.
Hoe de Excel VERT.ZOEKEN-functie te gebruiken| Dit is een van de meest gebruikte en populaire functies van Excel die wordt gebruikt om waarde op te zoeken uit verschillende bereiken en bladen.
Hoe de Excel te gebruiken? AANTAL.ALS-functie| Tel waarden met voorwaarden met behulp van deze geweldige functie. U hoeft uw gegevens niet te filteren om specifieke waarden te tellen. Countif-functie is essentieel om uw dashboard voor te bereiden.
Hoe de SUMIF-functie in Excel te gebruiken? | Dit is een andere essentiële functie van het dashboard. Dit helpt u bij het optellen van waarden voor specifieke voorwaarden.