Change dataformat from SQL in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-3071471%22%20slang%3D%22en-US%22%3EChange%20dataformat%20from%20SQL%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3071471%22%20slang%3D%22en-US%22%3E%3CP%3EHi.%20We%20have%20a%20problem%20converting%20a%20time%20stamp%20export%20from%20SQL%202019.%20Is%20there%20a%20way%20to%20convert%20it%20inside%20Excel%20(Not%20in%20the%20Database%20export)%20to%20a%20number.%20It%20has%20the%20format%20Year-MM-DD%20HH%3AMM%3ASS%2C000%20like%3A%202022-01-25%2009%3A54%3A55.803.%20Using%20Value%2C%20Format%20etc.%20does%20not%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20regards%2C%3C%2FP%3E%3CP%3EPreben%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3071471%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3071505%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20dataformat%20from%20SQL%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3071505%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F759605%22%20target%3D%22_blank%22%3E%40pbjerre%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%20Data%20%26gt%3B%20Get%20Data%20%26gt%3B%20From%20Other%20Sources%20%26gt%3B%20From%20Table%2FRange.%3C%2FP%3E%0A%3CP%3EExcel%20will%20probably%20convert%20the%20values%20to%20its%20own%20date%2Ftime%20format%20automatically.%3C%2FP%3E%0A%3CP%3EAlternatively%2C%20with%20such%20a%20value%20in%20D2%20and%20down%2C%20enter%20the%20following%20formula%20in%20another%20cell%20in%20row%202%2C%20then%20fill%20down%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DDATE(LEFT(A2%2C4)%2CMID(A2%2C6%2C2)%2CMID(A2%2C9%2C2))%2BTIMEVALUE(MID(A2%2C12%2C12))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EApply%20a%20custom%20date%2Ftime%20format%20to%20the%20cells%20with%20the%20formulas.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3073974%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20dataformat%20from%20SQL%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3073974%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDear%20Hans%3C%2FP%3E%3CP%3EThanks%20for%20the%20input.%20In%20the%20Data%26gt%3BGet%20Data%26gt%3B...%20the%20type%20is%20datetime%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20use%3A%26nbsp%3B%3CSPAN%3E%3DDATE(LEFT(A2%2C4)%2CMID(A2%2C6%2C2)%2CMID(A2%2C9%2C2))%2BTIMEVALUE(MID(A2%2C12%2C12))%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Eon%20the%20cell%20I%20get%20%23value!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESomething%20goes%20wrong.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3074026%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20dataformat%20from%20SQL%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3074026%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F759605%22%20target%3D%22_blank%22%3E%40pbjerre%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20should%20work%20with%20the%20example%20that%20you%20provided%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S1104.png%22%20style%3D%22width%3A%20737px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F342970i7DE218B220BBDEF4%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22S1104.png%22%20alt%3D%22S1104.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIf%20you%20want%20a%20number%20instead%20of%20a%20date%20and%20time%2C%20set%20the%20number%20format%20of%20the%20cell%20with%20the%20formula%20to%20General%20or%20to%20Number.%3C%2FP%3E%0A%3CP%3EI'd%20have%20to%20see%20a%20sample%20workbook%20to%20know%20why%20it%20doesn't%20work%20for%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3092969%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20dataformat%20from%20SQL%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3092969%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi.%20I%20get%20this%20error%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22pbjerre_2-1643641862519.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F343921i08789739E25CC9C2%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22pbjerre_2-1643641862519.png%22%20alt%3D%22pbjerre_2-1643641862519.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20am%20not%20able%20to%20upload%20an%20xls%20file%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
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

11 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