Format data to a different look

[rylv5050]

I receive a spreadsheet with data that I need to copy and paste this in to
another spreadsheet.

The data appears as follows in the following format:
1378:20:00
675:00:00
11:40:00
1:40:00
0:00

I need to write a formula or convert this into the following format:
1378.20
675.00
11.40
1.40
0.00

How can this be accomplished?

 

[Chip Pearson]

With a formula, you can use

=INT(A1*24)+(MINUTE(A1)/100)

To convert a lot of cells at once, use the following code:

Sub AAA()
Dim R As Range
For Each R In Selection.Cells
R.Value = Int(R * 24) + (Minute(R) / 100)
Next R
End Sub

Select the cell(s) you want to convert and then run the code. Be sure
to format the result cells as General or Numeric, not Date or Time.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Rick Rothstein]

I would think you can use this formula in another cell...

=TEXT(A1,"[h].mm")

and Copy/Paste Special/Values that column into your other worksheet.