Jan 25 2022 06:56 AM
Hi. We have a problem converting a time stamp export from SQL 2019. Is there a way to convert it inside Excel (Not in the Database export) to a number. It has the format Year-MM-DD HH:MM:SS,000 like: 2022-01-25 09:54:55.803. Using Value, Format etc. does not work.
Best regards,
Preben
Jan 25 2022 07:28 AM
Try Data > Get Data > From Other Sources > From Table/Range.
Excel will probably convert the values to its own date/time format automatically.
Alternatively, with such a value in D2 and down, enter the following formula in another cell in row 2, then fill down:
=DATE(LEFT(A2,4),MID(A2,6,2),MID(A2,9,2))+TIMEVALUE(MID(A2,12,12))
Apply a custom date/time format to the cells with the formulas.
Jan 27 2022 07:06 AM
Dear Hans
Thanks for the input. In the Data>Get Data>... the type is datetime
When I use: =DATE(LEFT(A2,4),MID(A2,6,2),MID(A2,9,2))+TIMEVALUE(MID(A2,12,12))
on the cell I get #value!
Something goes wrong.
Jan 27 2022 07:51 AM - edited Jan 27 2022 07:53 AM
It should work with the example that you provided:
If you want a number instead of a date and time, set the number format of the cell with the formula to General or to Number.
I'd have to see a sample workbook to know why it doesn't work for you.
Jan 31 2022 07:12 AM
Jan 31 2022 07:14 AM
Here is the sample xls file
Jan 31 2022 07:21 AM
Modified with two possible formulas:
Jan 31 2022 07:23 AM
Jan 31 2022 11:30 PM
Hm. It does not work when I press enter in the formular?? I dont see a diffence if I chose General or costum format. What could be the reason?
Jan 31 2022 11:44 PM
Feb 01 2022 12:28 AM
It depends on you regional settings. Thus use =DATE(...) + TIMEVALUE(...) but remove +0 at the end
Feb 01 2022 08:05 AM
Sep 29 2022 08:11 AM
I don't have separeted value. My SQL has this value Tue Jan 11 2022 00:00:00 GMT+0100 and I am trying to convert it into date but it is not working. Any advice please?
Best,
Sep 29 2022 08:47 AM
Let's say you get such a date value in D2. In another cell in row 2, enter the formula
=DATEVALUE(MID(D2,9,2)&"-"&MID(D2,5,3)&"-"&MID(D2,12,4))+TIMEVALUE(MID(D2,17,8))
and format the cell with the formula as date + time.
The formula can be filled down.