Update Time Format in Cells

reaktorblue

Board Regular
Joined
Aug 8, 2007
Messages
76
I have a macro that runs to clean up a report however, the values in the report are time and formatted with [hh]:mm:ss. The problem I run into is Excel does not seem to format this correctly until you click into the formula bar and press enter. When this is completed, it formats the value correctly and then I can do the proper calculations.

Is there a way to make this an automated function?
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Probably, but hard to say without seeing the code.

Can you post the code?
 

reaktorblue

Board Regular
Joined
Aug 8, 2007
Messages
76
The macro is very simple, it only deletes a few columns and rows that are not needed. Nothing else fancy takes place. The report itself is an export from our Cisco call reporting system and as it exports, it would say something like the time spent in available status would be 00:30:42 (signifying someone was available for 30 minutes and 42 seconds).

The issue is that the export is completely text, so when I format the cell to [hh]:mm:ss I have to click into the formula bar and press enter so that it automatically gives it the AM (it doesn't display that in the cell but it does in the formula bar)
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Without seeing your actual code, this is just a generalization of converting Text times to real times.
Based on your statement that simply entering the formula bar and pressing enter sucessfully converts it.

You can use

Code:
With Range("A1:A100")
    .Value = .Value
End With

The reason we would want to see your actual code is that there may be a way to adjust it.
So that the times are entered correclty in the first place, rather than adding an additional step to 'convert' them afterwards.
 

reaktorblue

Board Regular
Joined
Aug 8, 2007
Messages
76
I'll try that and see if that helps. The code in the macro is as follows.

Code:
    Rows("1:6").Delete Shift:=xlUp
    Rows("2:6").Delete Shift:=xlUp
    Rows("3:7").Delete Shift:=xlUp
    Rows("4:8").Delete Shift:=xlUp
    Rows("5:9").Delete Shift:=xlUp
    Rows("6:10").Delete Shift:=xlUp
    Rows("7:11").Delete Shift:=xlUp
    Rows("8:12").Delete Shift:=xlUp
    Rows("9:13").Delete Shift:=xlUp
    Rows("10:24").Delete Shift:=xlUp
    Columns("B:D").Delete Shift:=xlToLeft
    Columns("D:E").Delete Shift:=xlToLeft
    Columns("E:F").Delete Shift:=xlToLeft
    Columns("F:G").Delete Shift:=xlToLeft
    Columns("G:G").Delete Shift:=xlToLeft
    Columns("H:H").Delete Shift:=xlToLeft
 

reaktorblue

Board Regular
Joined
Aug 8, 2007
Messages
76
Without seeing your actual code, this is just a generalization of converting Text times to real times.
Based on your statement that simply entering the formula bar and pressing enter sucessfully converts it.

You can use

Code:
With Range("A1:A100")
    .Value = .Value
End With

The reason we would want to see your actual code is that there may be a way to adjust it.
So that the times are entered correctly in the first place, rather than adding an additional step to 'convert' them afterwards.
Your code is working beautifully. Thanks again for all the help, I greatly appreciate it!
 

Watch MrExcel Video

Forum statistics

Threads
1,102,302
Messages
5,486,053
Members
407,529
Latest member
netojose

This Week's Hot Topics

Top