converting :03:44 to 0:03:44

[honeywelljr]

I have a report that I am running and exporting into excel. The problem is that some of the values are units of time stated as :03:44 meaning 3min. 44sec. Excel refuses to see this as a number without a 0 for the hour. Is there a way using a macro, or change of format, or formula, or anything that would allow me to take that number and make is so that excel will recognize it as a unit of time that can be manipulated as a number?

 

[acw]

HI

Assuming that the data is in A1, how about
=VALUE("0" & A1)

This will give the time value, or can be formatted to show hours, mins and secs as required.


Tony

 

[Brian from Maui]

I have a report that I am running and exporting into excel. The problem is that some of the values are units of time stated as :03:44 meaning 3min. 44sec. Excel refuses to see this as a number without a 0 for the hour. Is there a way using a macro, or change of format, or formula, or anything that would allow me to take that number and make is so that excel will recognize it as a unit of time that can be manipulated as a number?

Try,

Custom format the cell that houses the formula as mm:ss

=--(0&A1)

 

[honeywelljr]

I guess a more direct problem is that it seems that excel will not recognize any of the times imported from the report as numbers. it only recognizes it as text, regardless of the formatting of the cell. Other numbers (i.e. percentages) are fine and can be used in formula's. Just not the intervals of time.