Want to convert a number that looks like time to text.

TomCon

Active Member
Joined
Mar 31, 2011
Messages
268
Office Version
365
Platform
Windows, Mobile
I have pasted a value that looks like "3:30" into Excel from the web. This number is actually an elapsed time and stands for 3 minutes and 30 seconds of elapsed time. As a number i would like it to be 3.5 (i.e. "three and a half minutes"). Also have numbers like 12:30, etc. meaning 12 minutes and 30 seconds, or 12.5 minutes.

In the formula bar, you see 3:30 AM when this cell is selected; Excel has interpreted this entry as time.
If you format it as a number, you get 0.146, i.e. the fraction of a day.

I cannot figure out a formula to convert this 3:30 (or 3:30AM, or .146) to the number 3.5. Any ideas?

I thought maybe i could treat it as text, search for the ":", and then divide it into the parts "3" and "30" and convert that way, but using the SEARCH function on this value, ":" is not found, as Excel considers it to be a number, not text.

Note, it is not practical to put an apostrophe before the value as it is a whole table of values (thousands) so that is not practical to do manually.

A formula that could take that 3:30 and convert to 3.5, or 12:40 and convert to 12.67, etc would be appreciated. Thanks!
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Watch MrExcel Video

Forum statistics

Threads
1,102,680
Messages
5,488,231
Members
407,632
Latest member
varunwalla

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top