Update Time Format in Cells

reaktorblue

Board Regular
Joined
Aug 8, 2007
Messages
87
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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)
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top