Forum Discussion
Change dataformat from SQL in Excel
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
14 Replies
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.
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.
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.
