Converting specific numbers into Date/Time using VBA Excel
Converting specific numbers into Date/Time using VBA Excel
I have some imported data and it looks like this: 43298,67569
supposing that first number (before comma) is DATE and after comma is Time(h/m):
17.07.2018 16:13 -> like this
Is there any kind of command, VBA or else that I can transfer this into normal DATE and TIME? So I tried with changing formats, also tried using Data->Text to Columns Option with different spacings and so on. Did not work.
And it is not only one cell, but like thousands (whole column) and make me a problem to make a certain Charts.
How does 67569 equate to 16:13?
– SJR
Aug 29 at 8:10
@SJR - It seems that M1rzÄ is in a country that switches decimals and commas, so a normal timestamp would read 43298.67569.
– SomethingDark
Aug 29 at 8:12
Comma is the decimal separator in Germany, so why don't you try to format the cell as date and time? Works for me. No VBA needed.
– Storax
Aug 29 at 8:13
I managed!!! Thank you guys, it was like in USER defined formats...damn :/
– M1rzÄ
Aug 29 at 8:21
1 Answer
1
You do not mention how you are importing the data. If you are frequently performing this import, you might want to consider using PowerQuery to do the import where you can use https://support.office.com/en-us/article/internationalization-power-query-d42b9390-1fff-413f-8120-d7df0ced20b9
Aside: PowerQuery in Excel 2016 is much improved in my opinion and worth checking out again if you were put off by Excel 2013
Yeah,,it will be always the same, tons of data, same amount, same graphs. Some minor changes could be expected! I did not use PQ so far, will check it out for myself! Thank you
– M1rzÄ
Aug 30 at 11:48
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Change the comma to a decimal
– SomethingDark
Aug 29 at 8:10