Convert Hours and Minutes
 

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?

Convert Hours and Minutes
 

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


"Beep Beep" wrote in message
...
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?

Convert Hours and Minutes
 

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


"Beep Beep" wrote:

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?

Convert Hours and Minutes
 

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


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


"Beep Beep" wrote:

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?

Convert Hours and Minutes
 

An excellent improvement!

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


"Rick Rothstein (MVP - VB)" wrote:

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


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


"Beep Beep" wrote:

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?

Convert Hours and Minutes
 

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


"Rick Rothstein (MVP - VB)" wrote:

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


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


"Beep Beep" wrote:

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?