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.

 

[ZVI]

Hi Clayton,
Conversion VBA-tool could be like this:

<font face=Courier New>
' Safe conversion from hh.mm double to hh:mm time
' Only numerical cells in the chosen range(s) will be converted
Sub Num2Time()
  Dim x As Range
  On Error Resume Next
  For Each x In Application.InputBox(Prompt:="Range for conversion", _
                            Title:="Num2Time", _
                            Default:=Selection.Address(0, 0), _
                            Type:=8)
                            
      If Not IsEmpty(x) And IsNumeric(x) And (x.NumberFormat <> "[h]:mm") Then
        x.Value = Int(x) / 24 + (x - Int(x)) * 100 / 1440
        x.NumberFormat = "[h]:mm"
      End If
      
  Next
End Sub
</FONT>

Regards,
Vladimir

 

[Claymationator]

Thanks Vladimir,

However, how would that code change since all of my times are in [m]:ss?

 

[xld]

Thanks Vladimir,

However, how would that code change since all of my times are in [m]:ss?

Did you try my amended version?

 

[Claymationator]

xld,

Thanks for the post, that seems to do the trick. I didn't see your post before, I just went straight to the second page of the thread. Thanks again.

Clay

 

[ZVI]

Thanks Vladimir,

However, how would that code change since all of my times are in [m]:ss?

The code for conversion of the double to [m]:ss would be like this:

<font face=Courier New>
Sub Num2Time()
  Dim x As Range
  On Error Resume Next
  For Each x In Application.InputBox(Prompt:="Range for conversion", _
                            Title:="Num2Time", _
                            Default:=Selection.Address(0, 0), _
                            Type:=8)
                            
      If Not IsEmpty(x) And IsNumeric(x) And (x.NumberFormat <> "[m]:ss") Then
        x.Value = Int(x) / 1440 + (x - Int(x)) / 864  ' 100 / 86400 =  1 / 864
        x.NumberFormat = "[m]:ss"
      End If
      
  Next
End Sub</FONT>

Vladimir