Forum Discussion

pbjerre's avatar
pbjerre
Copper Contributor
Jan 25, 2022

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:...
excel
Formulas and Functions
HansVogelaar's avatar
HansVogelaar
MVP
Jan 25, 2022

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.

pbjerre's avatar
pbjerre
Copper Contributor
Jan 27, 2022

HansVogelaar 

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.

 

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jan 27, 2022

    pbjerre 

    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.

    • pbjerre's avatar
      pbjerre
      Copper Contributor
      Jan 31, 2022

      HansVogelaar 

      Hi. I get this error:

      I am not able to upload an xls file

       

       

Resources