Time format

  • Thread starter Dushyant Rajput
  • 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
 
S

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
|
|
 
D

Dushyant Rajput

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
 
S

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
| > |
| > |
| >
| >
|
|
 
D

Dave Peterson

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.
 
D

Dushyant Rajput

*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...
 
Top