asked on

Excel Data connection import date datatype issue

I have an excel workbook setup, which when opened runs the sql script setup via data connections to sql server and loads the data in.
The problem is to do with date format on excel workbook. It always gets at 'text'
I am working with excel 2007.
I have tried returning the date from my db column as 'Date' datatype, tried setting format in excel as Date, still it gets as text.
When I double click the cell, and press enter, it sorts the date.
The problem with this is that, if its not date, the filters in excel work as 'text filter' and not as 'date filters'

any way to sort this out ?

ASKER CERTIFIED SOLUTION

leonstryker

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anastasia D. Gavanas

You could run a macro to change all cells in the specific column with the "edit-enter" trick, then select the row and format as a date.
See code attached below - just change column A to your column.

Sub changedates()
Dim ws As Worksheet
Set ws = Application.ActiveWorkbook.ActiveSheet
mycol = "A" 'Set your column
For i = 1 To ws.UsedRange.Rows.Count
    mycell = mycol & i
    ws.Range(mycell).Activate
    ws.Range(mycell).Select
    var1 = ActiveCell.FormulaR1C1
    ActiveCell.FormulaR1C1 = var1
    ws.Range(mycell).Select
    Selection.NumberFormat = "d/m/yyyy;@"
Next
End Sub

Open in new window

SOLUTION

Anastasia D. Gavanas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Davy2270

Just a tip when you need to do this only once and you don't want to write code.

Type 1 in a free cell.
Copy the cell.
Paste the value on the date list as values, also choose operation 'Divide' in the pastespecial dialogbox.
Your text dates will have changed to value dates.

Kind regards,
Davy