Format data to a different look
 

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?

Format data to a different look
 

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)


On Mon, 9 Feb 2009 10:10:01 -0800, rylv5050
wrote:

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?

Format data to a different look
 

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.

--
Rick (MVP - Excel)


"rylv5050" wrote in message
...
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?