Change dataformat from SQL in Excel

Occasional Contributor

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

@pbjerre 

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.

@Hans Vogelaar 

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.

 

@pbjerre 

It should work with the example that you provided:

S1104.png

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.

@Hans Vogelaar 

Hi. I get this error:

pbjerre_2-1643641862519.png

I am not able to upload an xls file

 

 

Here is the sample xls file

@pbjerre 

Modified with two possible formulas:

@pbjerre 

Try to apply General format and re-enter.

In my case simple =A2+0 works

image.png

@Sergei Baklan 

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?

pbjerre_0-1643700158527.png

 

Hi. Be sure that the separator in the formula is ' , ' or try to change to ' ; '. Regards

@pbjerre 

It depends on you regional settings. Thus use =DATE(...) + TIMEVALUE(...) but remove +0 at the end

@Hans Vogelaar 

Yes. Thanks. This worked

pbjerre_0-1643731520218.png

 

Hi @Hans Vogelaar 

 

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,

@klamaa 

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.

Many thanks Hans