Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lock Cell Format - Allow copy and paste of data without format change | Excel Worksheet Functions | |||
Increase Your Business By Data Conversion, Data Format and Data EntryServices in India | Excel Worksheet Functions | |||
Data Entry Online, Data Format, Data Conversion and Data EntryServices through Data Entry Outsourcing | Excel Discussion (Misc queries) | |||
Data in Cross-Tab format: needs to be written in Table Format | Excel Programming | |||
code for converting data format in another format | Excel Programming |