CODEWITHSUNDEEP
excelformatvba
user2408171
user2408171

Reputation: 37

Excel seems to change format of only some columns

Using VBA, I import a csv file and put a bunch of data into several columns.

One of these columns has a date and time. As I need to be able to use just the 'time' part of these cells, I try to convert the entire column to Time by using (and just about every other variation)

Cells(x.y).EntireColumn.NumberFormat = "hh:mm:ss"  
or   
Range("C1").NumberFormat = "hh:mm:ss"     
Range("C1").EntireColumn.NumberFormat = "hh:mm:ss"

However, this does not convert the entire column. I've tried every possible other way of selecting the entire column and changing it (through VB) however still only a portion remains converted.

If I doubleclick on these unconverted cells and press enter they change to the correct format. I realise this is a common problem relating to Calculations but my workbook is set to Automatic Calculations and I've tried setting this in VB too. This doesn't change anything.

The only pattern I can find is that the cells stop being converted when the Day reaches double digits. For example:

Column C 

01/05/2013 7:28:56  
03/05/2013 13:24:53  
07/05/2013 20:13:24  
09/05/2013 8:29:22  
12/05/2013 9:28:56  
15/05/2013 21:14:25  
17/05/2013 7:28:56

Becomes:

Column C 

7:28:56  
13:24:53  
20:13:24  
8:29:22  
12/05/2013 9:28:56  
15/05/2013 21:14:25  
17/05/2013 7:28:56

In the formula bar up the top for each cell it still shows the whole Date and Time for all cells, not sure if this is related, but doesn't seem to matter in terms of the calculations i have to perform using the Time.

Essentially I have to take the time for a cell in column C and the time from another Cell (also in Date/Time format) and check the difference. After some research I decided the best way was to convert all the cells to a time format and then do my calculations.

Alternatively I could try converting the column to text and using a Split function (using space as a delimiter) and pulling the time out, but I'm having trouble doing this too, as once again trying to convert the entire column to text stops at the double digits for date.

Thanks for reading through all that, any thoughts and help would be appreciated.

Edit: Realised some of my syntax was incorrect in my post, this was however correct inside my macro

another edit: I think this definitely has something to do with the date format... I just realised that before i format them, the dates are m/dd/yyyy and then when it gets to actual double digit days it changes to dd/mm/yyyy, and thats when the problem occurs...

Upvotes: 1

Views: 960

Answers (1)

MikeD
MikeD

Reputation: 8941

In order to avoid confusion, and as the date always seems to occupy the same width, I recommend to

1) import this column as a text

2) then go over the whole column

For Each C In Range("A:A").Cells
    If C <> "" Then
        ' ....
    End If
Next C

3) cut away the leading 11 positions, e.g. C = Mid(C, 11, 99)

4) convert the remaining string to a time, e.g. C = CDate(C) (... yes it works with a time as well, because a time is a fractional part of a date)

Alternatively you may want to capture the date part and bring it into shape, too. See here for some ideas using worksheet formulas.

Upvotes: 1

Related Questions