Convert Hours and Minutes

[Beep Beep]

I have a spreadsheet that has hours and minutes manually inputed. Is there a
formula that will convert the following: 38:90 (38 hours and 90 minutes) to
39 hours and 30 minutes?

 

[Rick Rothstein \(MVP - VB\)]

Probably not the most efficient, but this works...

=(LEFT(A1,FIND(":",A1)-1)+INT(MID(A1,FIND(":",A1)+1,4)/60))
&" hours and "&MOD(MID(A1,FIND(":",A1)+1,4),60)&" minutes"

Rick

 

[Gary''s Student]

=LEFT(A1,2)/24+RIGHT(A1,2)/(24*60) and format as [h]:mm

 

[Rick Rothstein \(MVP - VB\)]

What if the user enters 123:123? You formula can be patched like this...

=LEFT(A1,FIND(":",A1)-1)/24+MID(A1,FIND(":",A1)+1,9)/(24*60)

However, I assumed from the OP's post that he wanted the result to read like
a sentence (xx hours and yy minutes) and not like a time value.

Rick

=LEFT(A1,2)/24+RIGHT(A1,2)/(24*60) and format as [h]:mm
--
Gary''s Student - gsnu200783

I have a spreadsheet that has hours and minutes manually inputed. Is
there a
formula that will convert the following: 38:90 (38 hours and 90 minutes)
to
39 hours and 30 minutes?

 

[Gary''s Student]

An excellent improvement!

Any idea of why the TIME() function does not work??
--
Gary''s Student - gsnu200783

What if the user enters 123:123? You formula can be patched like this...

=LEFT(A1,FIND(":",A1)-1)/24+MID(A1,FIND(":",A1)+1,9)/(24*60)

However, I assumed from the OP's post that he wanted the result to read like
a sentence (xx hours and yy minutes) and not like a time value.

Rick

=LEFT(A1,2)/24+RIGHT(A1,2)/(24*60) and format as [h]:mm
--
Gary''s Student - gsnu200783

I have a spreadsheet that has hours and minutes manually inputed. Is
there a
formula that will convert the following: 38:90 (38 hours and 90 minutes)
to
39 hours and 30 minutes?

 

[Rick Rothstein \(MVP - VB\)]

An excellent improvement!

Thanks!

Any idea of why the TIME() function does not work??

My guess is that the 90, being greater than the maximum number of minutes
for the minutes field, makes it look like plain text to Excel rather than a
time (apparently colons alone is not enough to make Excel treat an entry as
a time value).

Rick

Gary''s Student - gsnu200783

What if the user enters 123:123? You formula can be patched like this...

=LEFT(A1,FIND(":",A1)-1)/24+MID(A1,FIND(":",A1)+1,9)/(24*60)

However, I assumed from the OP's post that he wanted the result to read
like
a sentence (xx hours and yy minutes) and not like a time value.

Rick


message

=LEFT(A1,2)/24+RIGHT(A1,2)/(24*60) and format as [h]:mm
--
Gary''s Student - gsnu200783


:

I have a spreadsheet that has hours and minutes manually inputed. Is
there a
formula that will convert the following: 38:90 (38 hours and 90
minutes)
to
39 hours and 30 minutes?