Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Excel appending Date

Options
  • 04-10-2007 12:55pm
    #1
    quinnd6
    Registered Users Posts: 1,552 ✭✭✭


    I need to append a date to an insert statement for a sql table in in excel.

    The problem is when I try it the date is showing up as a number and not a date. 
     ="insert into users(Username,created,d_id,c_id,ad_id) values ('" & B3 & "', " & D3 &" , '"& C3 &"', "& F3 &", "& G3 &" ); '"


    D3 is supposed to be the date but it shows up as a number when I try it.

    Can you help?


Comments

  • Options
    #2
    irlrobins
    Registered Users Posts: 7,541 ✭✭✭irlrobins


    Microsoft Excel stores dates as sequential numbers known as serial values and stores times as decimal fractions because time is considered a portion of a day. Dates and times are values and so you can add, subtract, and include dates and times in other calculations.

    This simply means that the date 1 Jan 1900 has a true numeric value of 1, 2 Jan 1900 has a value of 2 etc.


  • Options
    #3
    Ginger
    Registered Users Posts: 2,931 ✭✭✭Ginger


    Is any of the data null in the date column.

    Excel generally trys to do funky things with the date because there is no column types. So it can coerce all the values into a standard type.

    Have a check in your registry for the following keys

    Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows
    Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ImportMixedTypes

    TypeGuessRows: setting the value to 0 (zero) will force ADO to scan
    all column values before choosing the appropriate data type.

    You can also try IMEX=1 in the connection string

    Might help.


  • Options
    #4
    Edser
    Registered Users Posts: 363 ✭✭Edser


    Not pretty but..

    Try replacing D3 with DAY(D3)&"/"&MONTH(D3)&"/"&YEAR(D3)


    New formula : ="insert into users(Username,created,d_id,c_id,ad_id) values ('" & B3 & "', " & DAY(D3)&"/"&MONTH(D3)&"/"&YEAR(D3) &" , '"& C3 &"', "& F3 &", "& G3 &" ); '"


    Ed


  • Options
    #5
    ruaidhrimurray
    Closed Accounts Posts: 1 ✭ruaidhrimurray


    Hi mate,
    Can you help with the following please
    I have a file exported from our Payroll system which shows 30 hours in the cell but when you click in it states the following '01/01/1900 07:00:00'
    Can you help to get this to a numbers format so I can total a series of hours as need to total a spreadsheet - i'm not able to use code so hope you know another way
    Cheers
    R


  • Options
    #6
    Edser
    Registered Users Posts: 363 ✭✭Edser


    The datatime example you give is a bit strange because it does not correspond to 30, it goes to 1.29 but there are a couple of things you can try..

    Firstly, try right-clicking on the cell(s), click 'Format Cells', then choose General and click OK. This should change the date/time to a number.

    Alternatively, try formatting the to general (as above) and pasting the data in from the export file.

    Ed


  • Advertisement
Advertisement