We help IT Professionals succeed at work.

Excel Data connection import date datatype issue

cynx
cynx asked
on
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 ?
Comment
Watch Question

>any way to sort this out ?

Since you are using data connections the only real way to do this is to reformat the data after it is brought into the spreadsheet. Something like this should do the trick:

Sub ConvertDate()
    Columns(4).FormulaR1C1 = Columns(4).Value
End Sub
Top Expert 2011

Commented:
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

Top Expert 2011
Commented:
Minor corrections, if you want to change formulas to values and then format to DATE
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.Value
    ActiveCell.FormulaR1C1 = var1
    ws.Range(mycell).Select
    Selection.NumberFormat = "d/m/yyyy;@"
Next
End Sub

Open in new window

Commented:
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