Converting Number to Time

[Claymationator]

I am trying to compile data from several different managers within my company. They all have similar workbooks, and have been entering time in a number format. Currently they have values that look like this:

Book1
	A	B	C	D
1	1.35	1.18
2	2.01	2.23
3	2.33	3.05
4	4.12	4.22
Sheet1

However, the data should appear as this:

Book1
	D	E	F	G
1	01:35	01:18
2	02:01	02:23
3	02:33	03:05
4	04:12	04:22
Sheet1

So, in any instance the period or decimal point should be a colon and the format of the cells should be in [m]:ss.

I want to be able to select a range within the workbook and run a macro that will convert the numbers into the "[m]:ss format.

Any help is appreciated.

Thanks.

 

[xld]

Public Sub ChangeTimes()
Dim cell As Range
  
    For Each cell In Selection
    
        cell.Value = Replace(cell.Value, ".", ":")
        cell.Value = cell.Value / 60
        cell.NumberFormat = "[m]:ss"
    Next cell
End Sub

 

[Claymationator]

Thanks, that just about does it. However, there are a few instances where it doesn't seem to work just right in the conversion. That is when the initial value ends in a "0".

For intance:

0.50 changes to 0:05 instead of 0:50
1.50 changes to 1:05 instead of 1:50

Is there a way around that?

 

[Analyze_This]

You don't need to use VBA. Just use the text to columns feature to seperate the data (use period as the delimiter) and then join the two back to gether with concatination. i.e. =TEXT(A2&":"&B2,"h:mm")

 

[Jonmo1]

Or a formula

=TIME(0,INT(A1),MOD(A1,1)*100)

 

[Claymationator]

You don't need to use VBA. Just use the text to columns feature to seperate the data (use period as the delimiter) and then join the two back to gether with concatination. i.e. =TEXT(A2&":"&B2,"h:mm")

Or a formula

=TIME(0,INT(A1),MOD(A1,1)*100)

Thanks for those responses. I have been using a similar formula, but the way that the spreadsheet are set up make the formulas very time consuming, where VBA would be a quicker process.

 

[schielrn]

Why not use the current formula you are using in VBA then, either using the evaluate method or using the worksheetfunction code within the VBA?

 

[Claymationator]

Why not use the current formula you are using in VBA then, either using the evaluate method or using the worksheetfunction code within the VBA?

schielrn,

Thanks for the post. Could you possibly get me started on that? I haven't used the evaluate or worksheetfunction modes before. Jonmo's formula is a little neater than mine was, so I have changed the formula to:

=TIME(0,INT(A1),MOD(A1,1)*100)

 

[schielrn]

Maybe try:

sub test()
for each c in range("a1:b4")
c.value = worksheetfunction.text(Evaluate("TIME(0,INT(" & c.Value & "),MOD(" & c.Value & ",1)*100)"), "[m]:ss")
next c
end sub

Hope that helps. Modify as needed. You may need to get rid of the worksheetfunction.text part and just let them come in as decimals and then format separately if you are doing calculations because it may be stored as h:mm and not actually m:ss?

 

[xld]

Thanks, that just about does it. However, there are a few instances where it doesn't seem to work just right in the conversion. That is when the initial value ends in a "0".

For intance:

0.50 changes to 0:05 instead of 0:50
1.50 changes to 1:05 instead of 1:50

Is there a way around that?

Public Sub ChangeTimes()
Dim cell As Range
  
    For Each cell In Selection
    
        cell.Value = Replace(cell.Text, ".", ":")
        cell.Value = cell.Value / 60
        cell.NumberFormat = "[m]:ss"
    Next cell
End Sub