Cells Formatting for DATE

[avadhutd2]

Hi,

I need a help for formatting the DATE fields in a sheet which appears in General Format.

The situation is as follows -

• I am fetching data in columns from DB (Total 55 in DB out of which few or all are selected by the user). There are few date columns (like Arr_DT, Dep_DT etc)
• After getting data in Sheet, the cell format for DATES is seen as general. For example - We can see date as 38500 instead of 5/28/05.

I tried following code, but most of the columns are getting converted to DATE format.

Cells.Find(What:="_DT", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _, SearchFormat:=False).Activate

ActiveCell.Columns.EntireColumn.Select
Selection.NumberFormat = "mm/dd/yy ;@"

For i = 0 To ListBox1.ListCount
' This is the count of columns selected by USER. i.e This many times I need to iterate & see whether there are any columns having "_DT" text.


Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Columns.EntireColumn.Select
Selection.NumberFormat = "mm/dd/yy ;@"
Next


What I need is ...if user selects 25 columns & there are 3 DATE columns, then using this code we should be able to identify these 3 DATE fields & then format only those columns.
......In short only DATE fields should be identified and formatted & OTHERS should be remaining in the same format as got from DB.

Currently I am getting some wrong results...Can anyone guide me here or propose any other alternative to acheive this requirement?

Thanks!

[nst1107]

I ran your code and it seemed to work fine. What sort of "wrong results" are you getting? Can you post a workbook with some sample data that is similar to what you are working with?

[Paul_Hossler]

If you're bring in the data from an external source, 38500 might be text = "38500".

Inside a loop, try making each cell a number:

.Value = --.Value

and see if the date format takes

Paul

[Bob Phillips]

Paul,

-- is an Excel construct, not VBA.

.Value = .Value

should sort it out.

[Paul_Hossler]

Paul,

-- is an Excel construct, not VBA.

.Value = .Value

should sort it out.

At least in 2007, either seems to actually work, but your's is shorter.

The important thing is to get the values into numbers so that a Date format will work.

[VBA]
Sub drv()

With Worksheets("sheet1").Cells(1, 1)
.Clear
.NumberFormat = "General"
.Value = "'38500"
MsgBox TypeName(.Value)

' .Value = .Value
' MsgBox TypeName(.Value)

.Value = --.Value
MsgBox TypeName(.Value)

End With
End Sub
[/VBA]

Paul

[Bob Phillips]

Of course it does, it is truly double-negating LOL!

[Paul_Hossler]

Of course it does, it is truly double-negating LOL!

I was surprised that setting a .Value containing a string to itself would do a type conversation and make it into a double

I knew from previous posts that VBA would do the type conversion for a 'numeric string' to a number if you used the negation operator, and another to flip the sign of the 'now a number' again.

Paul