# Thread: Want to convert a number that looks like time to text. Thanks: 0 Likes: 0

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

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!

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

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

=A1*24
And format as a NUMBER.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•