Excel Time Calculation Excel and time... not the best, but I hope someone can he

Question

Excel Time Calculation

Excel and time... not the best, but I hope someone can help me get this up and running

I want 1 formula that would be able to do the following:

turn Hr:m:S into just minutes

so 1:02:39 = 62.65 minutes

I can do so with the formula =HOUR(D2)*60+MINUTE(D2)+SECOND(D2)/60

The problem is, I have a series of numbers that are not in Hr:M:S: they are in M:S so the formula i used above will give me the wrong time

so 36:15 = 735 instead of 36.25 minutes.

My thought, was to get an if function to determine if the cell is in the following format HR:M:S.

that is where i am having the problem, I cant find a way to use an if function to determine the format of the cell.

This is my data i want to convert to minutes
11:08      
1:02:39
1:25
0:00
36:15
19:01
20:08
0:06
0:14

is there a way to use 1 formula to get my desired solution? again, i'm thinking about using an if formula, but I dont know how to get it to look at the format of the cell.

Explanation / Answer

@When you enter:"

36:15

Excel says to itself "Hours and minutes"

So, instead, enter

0:36:15

and Excel will properly say "Hours, minutes, and seconds"

You can try a formula like this, if the values are actually strings:

=IF(LEN(D2)=7,D2*24,D2*1440)

or if they are actually times, use

=IF(D2>2/24,D2*24,D2*1440)

Change the 2 in the 2/24 to the maximum hours that you would actually enter

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.