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!
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!
