VBA convert d.hh:mm:ss to hh:mm:ss

[adryy_yy]

Hello,

I'm new here and I need your help because I understand something Excel but VBA I don't understand anything.
So, if you can help me I say thanks so much.

And then, I have a sheet Excel with values in next format: "dd.hh:mm:ss". I would need to convert to "hh:mm:ss", but on VBA. Could you help me?

Thanks

 

[tdh777]

Hi.

Are you sure you need to do the conversion of format in VBA ?

If you "understand Excel" but "don't understand VBA" why not just adjust the format of the cells to "hh:mm:ss" using the Format Cells Function ?

Because if you DID adjust the format using VBA you will get exactly the same result.

And if you turn on the Macro recorder before doing it you will get the VBA code to make the change when you look at the Macro.

Much quicker than waiting for an answer here.

Cheers.
Warren K.

 

[adryy_yy]

Hi.

Are you sure you need to do the conversion of format in VBA ?

If you "understand Excel" but "don't understand VBA" why not just adjust the format of the cells to "hh:mm:ss" using the Format Cells Function ?

Because if you DID adjust the format using VBA you will get exactly the same result.

And if you turn on the Macro recorder before doing it you will get the VBA code to make the change when you look at the Macro.

Much quicker than waiting for an answer here.

Cheers.
Warren K.

Hello Warren K.,

How you adjust this format using Format cells? I tried and I didn't obtain this.

I tried the next formul "=24*LEFT(D33;SEARCH("."; D33)-1)+HOUR(TIMEVALUE(RIGHT(D33;LEN(D33)-SEARCH(".";D33)))) &":" & MINUTE((TIMEVALUE(RIGHT(D33;LEN(D33)-SEARCH("."; D33))))) & ":" & SECOND((TIMEVALUE(RIGHT(D33;LEN(D33)-SEARCH("."; D33)))))" and I obtain the "value". Therefore, I don't get to convert this "value" to real value or time. Stay as text or string. I will need this value to add or sub others values.

 

[FormR]

I tried the next formul...

Hi and welcome to the forum!

Here is another forumula you can try if all of your duration values are text and the days never exceed 31.

=0+SUBSTITUTE(D33;".";"JAN1900 ")

Custom format the cells that contain the formula as

[hh]:mm:ss

 

[adryy_yy]

Hi and welcome to the forum!

Here is another forumula you can try if all of your duration values are text and the days never exceed 31.

=0+SUBSTITUTE(D33;".";"JAN1900 ")

Custom format the cells that contain the formula as

[hh]:mm:ss

Thanks FormR...

With your code I get this:

Sub Replace()

Dim rng As Range, cell As Range
Set rng = Sheets("Sheet1").Range("D2:D629")

For Each cell In rng
cell = WorksheetFunction.Substitute(cell, ".", "JAN1900 ")
Next
End Sub

Now, I obtain this values:

Just fault the next "=0+" in code... Could you help me with this? The "0+" do all difference...

 

[FormR]

Hi, try adding:

cell.NumberFormat = "[hh]:mm:ss"

After the "cell = worksheetfunction.." line.

 

[tdh777]

Hi again,

So you have a TEXT String eg "4.09:03:32" and you want to convert it into an Excel TIME value ? Is that right ?

And some of your input values do not have any leading days on them , right ? eg "00:00:56"

If so, try this :

=IFERROR(LEFT(D33,FIND(".",D33)-1)+TIMEVALUE(MID(D33,FIND(".",D33+1,99)),TIMEVALUE(D33))

That result is an excel time-value (number of days) as a decimal and works on all your sample-data.

Regards,
Warren K.

 

[FormR]

try adding

And here is a different VBA approach you can try:

Sub MyReplace()
With Sheets("Sheet1").Range("D2:D629")
  .Replace What:=".", Replacement:="Jan 1900 ", LookAt:=xlPart, SearchFormat:=False, ReplaceFormat:=False
  .NumberFormat = "[hh]:mm:ss"
End With
End Sub

 

[adryy_yy]

And here is a different VBA approach you can try:

Sub MyReplace()
With Sheets("Sheet1").Range("D2:D629")
  .Replace What:=".", Replacement:="Jan 1900 ", LookAt:=xlPart, SearchFormat:=False, ReplaceFormat:=False
  .NumberFormat = "[hh]:mm:ss"
End With
End Sub

I tried with line you give me and... Perfect... :D

I will try with this new code.

Thanks a lot :D

 

[adryy_yy]

And here is a different VBA approach you can try:

Sub MyReplace()
With Sheets("Sheet1").Range("D2:D629")
  .Replace What:=".", Replacement:="Jan 1900 ", LookAt:=xlPart, SearchFormat:=False, ReplaceFormat:=False
  .NumberFormat = "[hh]:mm:ss"
End With
End Sub

These code is just applicable to d.hh:mm:ss but in column there are d.hh:mm:ss and hh:mm:ss and I need apply in all cells of the column.

One more time, thank you...