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

adryy_yy

New Member
Joined
Mar 12, 2018
Messages
6
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?

123m.jpg


Thanks
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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.
 
Upvote 0
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.
 
Upvote 0
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
Code:
[hh]:mm:ss
 
Upvote 0
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
Code:
[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:

153m.jpg


Just fault the next "=0+" in code... Could you help me with this? The "0+" do all difference...
 
Upvote 0
Hi, try adding:

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

After the "cell = worksheetfunction.." line.
 
Last edited:
Upvote 0
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 :
Code:
=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.
 
Upvote 0
try adding

And here is a different VBA approach you can try:

Code:
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
 
Upvote 0
And here is a different VBA approach you can try:

Code:
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
 
Upvote 0
And here is a different VBA approach you can try:

Code:
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... :p
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,183
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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