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

#### TomCon

##### Active Member
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!

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

#### Forever_EX

##### New Member
Ok Try this formula, assuming 3:30 is in A1:

And format as a NUMBER.

Replies
3
Views
30
Replies
2
Views
52
Replies
3
Views
36
Replies
11
Views
132
Replies
2
Views
86