Time format

  • Thread starter Thread starter Dushyant Rajput
  • Start date Start date
D

Dushyant Rajput

Hi,

my data looks like 01:00:00 ( it ok as its in hh:mm:ss format) but my
problem is with when my data comes out to be like :37:30 (General format) no
I want this to be convert in to hh:mm:ss for which I need to add 0 at
start..
Any formulae or Macro which can help me to sort this out.

Thanks
 
Assuming that there aren't any other defects in the data and that your data
is in ColA, try putting this formula in ColB (and formatting ColB to
"hh:mm:ss") ...

=IF(LEFT(A1,1)=":",VALUE(0&A1),A1)

Rgds,
ScottO

| Hi,
|
| my data looks like 01:00:00 ( it ok as its in hh:mm:ss format) but my
| problem is with when my data comes out to be like :37:30 (General format)
no
| I want this to be convert in to hh:mm:ss for which I need to add 0 at
| start..
| Any formulae or Macro which can help me to sort this out.
|
| Thanks
|
|
 
so sweeeet ScottO..
can you help me to get this into macro because my raw data is so huge.
assuming that there is a mix up of hh:mm:ss and general in the same column.
" Macro that convert :mm:ss into hh:mm:ss any data in the sheet".
Thnx again ScottO
 
Sorry, I'm no good at macros.
Can someone else help please ....
ScottO


| so sweeeet ScottO..
| can you help me to get this into macro because my raw data is so huge.
| assuming that there is a mix up of hh:mm:ss and general in the same
column.
| " Macro that convert :mm:ss into hh:mm:ss any data in the sheet".
| Thnx again ScottO
|
|
| | > Assuming that there aren't any other defects in the data and that your
| > data
| > is in ColA, try putting this formula in ColB (and formatting ColB to
| > "hh:mm:ss") ...
| >
| > =IF(LEFT(A1,1)=":",VALUE(0&A1),A1)
| >
| > Rgds,
| > ScottO
| >
| > | > | Hi,
| > |
| > | my data looks like 01:00:00 ( it ok as its in hh:mm:ss format) but my
| > | problem is with when my data comes out to be like :37:30 (General
| > format)
| > no
| > | I want this to be convert in to hh:mm:ss for which I need to add 0 at
| > | start..
| > | Any formulae or Macro which can help me to sort this out.
| > |
| > | Thanks
| > |
| > |
| >
| >
|
|
 
How about something like:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range

Set myRng = Selection

myRng.NumberFormat = "hh:mm:ss"

For Each myCell In myRng.Cells
With myCell
If IsNumeric(.Value) Then
'do nothing
ElseIf Left(.Value, 1) = ":" Then
.Value = "0" & .Value
End If
End With
Next myCell
End Sub

Select your range first and run the macro.
 
*Dave* You Rock...

Thanks a ton..
Dushyant

Dave Peterson said:
How about something like:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range

Set myRng = Selection

myRng.NumberFormat = "hh:mm:ss"

For Each myCell In myRng.Cells
With myCell
If IsNumeric(.Value) Then
'do nothing
ElseIf Left(.Value, 1) = ":" Then
.Value = "0" & .Value
End If
End With
Next myCell
End Sub

Select your range first and run the macro.
Click to expand...
 
Back
Top