Creating a chart to convert hundredths to minutes

My company is using a manual hundredths to minutes chart to change decimals
to minutes. Example if the hours worked shows as 1.83, the .83 is converted
to :50 minutes.
I am using a formula to calculate hours worked for the AM and PM routes with
a sign on and sign off time for each. The formula looks like this
=(D11-C11)*24 Sign on time is 5:20-C11, sign off time is 9:25 D11, hours
=4.08 The .08 in hundredths =05 minutes. At one point I use this formula to
display 4.08 in h:mm format, =TEXT(D11-C11,"h:mm") This returns 4:05. Is
there a way to create a lookup table that contains the hundredths to minutes
data then the cell with the value in hundredths look up and return the
corresponding value?

To create a time conversion chart that you could print out:

In column A, list the hundredths, e.g.:

0.01
0.02

In cell B2, enter the formula: =A2*60
Copy the formula down to the last row of data.

For your timesheet calculations, you can find lots of information on
date/time arithmetic on Chip Pearson's site:

http://www.cpearson.com/excel/datetime.htm

dvonj wrote:
> My company is using a manual hundredths to minutes chart to change decimals
> to minutes. Example if the hours worked shows as 1.83, the .83 is converted
> to :50 minutes.
> I am using a formula to calculate hours worked for the AM and PM routes with
> a sign on and sign off time for each. The formula looks like this
> =(D11-C11)*24 Sign on time is 5:20-C11, sign off time is 9:25 D11, hours
> =4.08 The .08 in hundredths =05 minutes. At one point I use this formula to
> display 4.08 in h:mm format, =TEXT(D11-C11,"h:mm") This returns 4:05. Is
> there a way to create a lookup table that contains the hundredths to minutes
> data then the cell with the value in hundredths look up and return the
> corresponding value?
>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

If what you are trying to do is keep the decimal value in the cell and
display hours and minutes, use formatting. It is much faster than a
lookup, especially if your data will increase to several hundred rows.

Right-click the formula cell, click format cells and select 'Time' and
'13:30'. If you do this to cells C1 and D1 as well, you must remove the
*24 from your formula.

HTH


On Fri, 11 Mar 2005 19:38:31 -0800, dvonj wrote:

> My company is using a manual hundredths to minutes chart to change decimals
> to minutes. Example if the hours worked shows as 1.83, the .83 is converted
> to :50 minutes.
> I am using a formula to calculate hours worked for the AM and PM routes with
> a sign on and sign off time for each. The formula looks like this
> =(D11-C11)*24 Sign on time is 5:20-C11, sign off time is 9:25 D11, hours
> =4.08 The .08 in hundredths =05 minutes. At one point I use this formula to
> display 4.08 in h:mm format, =TEXT(D11-C11,"h:mm") This returns 4:05. Is
> there a way to create a lookup table that contains the hundredths to minutes
> data then the cell with the value in hundredths look up and return the
> corresponding value?

--
Mike Argy
Custom Office solutions
and Windows/UNIX programs.