Help with Time Converstion Formula

[NeedhelpinTennessee]

I have created a scheduler which works great, but, now my boss wants me to
convert the final hours worked into 100's. My formula is as follows now:

=(I2-E2)*24-H2

Can someone please help me with a formula that converts 100's into minutes,
such as .14 hundreds.

Thanks,

 

[Myrna Larson]

If you mean that 0.14 represents 14/100 of an hour, and you want to convert
that to minutes: multiply by 24 (there are 24 hours in a day) and format as
time.

Or, to get actual minutes (not an Excel time), multiply 0.14 * 60.

 

[NeedhelpinTennessee]

I tried putting in the following formula, but, it did not work (actual time
worked was 6.14) my original formula was set as:

=(I2-E2)*24-H2

I tried putting the following:

=(I2-E2)*0.14*100-H2

it gave me 2.9 instead of the total 6.14

 

[Myrna Larson]

Actually, in my first reply, I said to multiply 0.14 by 24 and format as time.
You must DIVIDE by 24 and format as time. Sorry for the error.

But that seems not to be the problem, right?

I assume your formula calculates decimal hours, and H2 contains a value that
is expressed in hours rather than as an Excel time.

Assuming the time worked is calculated by the formula you show, and works out
to 6 + 14/100 hours:

If you want to display that as a time, you have to convert it back to an Excel
time, in which the units are days, not hours. You do that by dividing by 24
and set the cell format to hh:mm, i.e.

=((I2-E2)*24-H2)/24

or

=I2-E2-H2/24

If you want the number of minutes that correspond to 0.14 of an hour, you get
that by multiplying by the number of minutes in an hour, 60.

Original formula:

=(I2-E2)*24-H2 which gives whole and decimal hours (6.14)

if you want whole hours in one cell and minutes in another

=INT((I2-E2)*24-H2) for the hours (6)

=MOD((I2-E2)*24-H2,1)*60 for the minutes (works out to 8.4 minutes)

If none of this is correct, what is in I2, what is in E2, and what is in H2,
and what result do you want?

 

[NeedhelpinTennessee]

Thank you

Actually, in my first reply, I said to multiply 0.14 by 24 and format as time.
You must DIVIDE by 24 and format as time. Sorry for the error.

But that seems not to be the problem, right?

I assume your formula calculates decimal hours, and H2 contains a value that
is expressed in hours rather than as an Excel time.

Assuming the time worked is calculated by the formula you show, and works out
to 6 + 14/100 hours:

If you want to display that as a time, you have to convert it back to an Excel
time, in which the units are days, not hours. You do that by dividing by 24
and set the cell format to hh:mm, i.e.

=((I2-E2)*24-H2)/24

or

=I2-E2-H2/24

If you want the number of minutes that correspond to 0.14 of an hour, you get
that by multiplying by the number of minutes in an hour, 60.

Original formula:

=(I2-E2)*24-H2 which gives whole and decimal hours (6.14)

if you want whole hours in one cell and minutes in another

=INT((I2-E2)*24-H2) for the hours (6)

=MOD((I2-E2)*24-H2,1)*60 for the minutes (works out to 8.4 minutes)

If none of this is correct, what is in I2, what is in E2, and what is in H2,
and what result do you want?