CODEWITHSUNDEEP
exceltimeformula
AENick
AENick

Reputation: 331

How to convert two time formats into one?

I am pulling Cross Country race times, and trying to convert the race time into an integer that can be easier to work with.

I am using the formula 

=([Cell Name]-INT([Cell Name])*60*24

But I am so far retrieving two different time formats:

Both formats take that formula differently, and they are producing different numbers. The first format is changed into 17 minutes, but the other is turned into 1000+ minutes. Is there a way that I can convert these times into one format?

Upvotes: 0

Views: 43

Answers (1)

Forward Ed
Forward Ed

Reputation: 9874

The main problem you are having is as tom sharpe pointed out is that some times are being assessed as HH:MM and others as MM:SS. if you look at your source data cell (assmue A2) and use the following formula it will tell you if you are dealing with text or time in excel serial date time formatted to appear in a manner we are used to seeing:

=ISTEXT(A2)

now assuming that the result is TRUE, this means you are dealing with TEXT. Adding a leading 0: to the text will make the time recognizable to excel as being in the format HH:MM:SS and allow for you to deal with it consistently.

To do this use the following formula:

=timevalue("0:"&A2)

Things may get a little wonky if ou have anything greater than or equal to 60 as the first two digits.

once you have it in the proper format you can then use it in your formula.

Upvotes: 1

Related Questions