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

TomCon

Active Member
Joined
Mar 31, 2011
Messages
270
Office Version
  1. 365
Platform
  1. Windows
  2. 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

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

Watch MrExcel Video

Forum statistics

Threads
1,108,801
Messages
5,524,965
Members
409,612
Latest member
Jagadeeswaran Stalin

This Week's Hot Topics

Top