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


"Beep Beep" <(E-Mail Removed)> wrote in message
news:2654C9B7-B120-45B5-885A-(E-Mail Removed)...
>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?

=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?

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" <(E-Mail Removed)> wrote in message
news:E268B16E-94BB-4340-A350-(E-Mail Removed)...
> =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?

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" <(E-Mail Removed)> wrote in message
> news:E268B16E-94BB-4340-A350-(E-Mail Removed)...
> > =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?
>
>

> 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" <(E-Mail Removed)> wrote in
>> message
>> news:E268B16E-94BB-4340-A350-(E-Mail Removed)...
>> > =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?
>>
>>