Add 0 for hour in hh:mm:ss format

  • Thread starter Thread starter caseyoconnor10
  • Start date Start date
C

caseyoconnor10

I have a column of times formatted to hh:mm:ss. However the data i
exported from a database table. When it comes in the data does no
always have a value for the hours digit. Here is an example of my dat
[ :10:48 ] and [ 1:28:53 ] if the time was 10 minutes and 48 second
there is no place holder of 0 for the hours but if the time is 1 hou
28 minutes and 53 seconds, the place holder is 1. I need to be able t
format my fields so that a zero fills in if the time is less that
hour. I have tried custom formating but can not seem to get Excel t
add a zero as a place holder. We are having to manually add a zero int
our data and it is very time consuming. Please Hel
 
Hi,

Use a formula like

=TIMEVALUE(IF(LEFT(A9)=":",0&A1,TEXT(A1,"hh:mm")))

Then Copy Paste Special as Values.

Posted via: http://www.ozgrid.com
Excel Templates Training Add-ins.
Free Excel Forum & Business Software
 
One way, using a macro:

Public Sub ConvertToTime()
Dim rCell As Range
Dim rTextCells As Range
On Error Resume Next
Set rTextCells = ActiveSheet.Cells.SpecialCells( _
xlCellTypeConstants, xlTextValues)
On Error GoTo 0
If Not rTextCells Is Nothing Then
For Each rCell In rTextCells
With rCell
If .Text Like ":##:##" Then
.Value = TimeValue("0" & .Text)
.NumberFormat = "hh:mm:ss"
End If
End With
Next rCell
End If
End Sub

Formatting won't work because the entries starting with ":" are
interpreted as Text, not times (which are numeric). This macro converts
the text to a time and formats the cell.
 
I want to thank everyone....you were a huge help....I took everyone
ideas and customized a script that worked...I would not have been abl
to do this without your hel
 
Back
Top