Excel appending Date

quinnd6

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?

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.

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.

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

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

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