Convert Hours and Minutes

  • Thread starter Thread starter Beep Beep
  • Start date Start date
B

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?
 
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
 
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 said:
=LEFT(A1,2)/24+RIGHT(A1,2)/(24*60) and format as [h]:mm
--
Gary''s Student - gsnu200783


Beep Beep said:
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?
Click to expand...
Click to expand...
 
An excellent improvement!

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


Rick Rothstein (MVP - VB) said:
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 said:
=LEFT(A1,2)/24+RIGHT(A1,2)/(24*60) and format as [h]:mm
--
Gary''s Student - gsnu200783


Beep Beep said:
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?
Click to expand...
Click to expand...
Click to expand...
 
An excellent improvement!

Thanks!
Any idea of why the TIME() function does not work??
Click to expand...

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) said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top