Excel 97 – Showing a value in hours and minutes

  GroupFC 11:36 03 Mar 2003

I am a relatively new user and wonder if anybody can advise me how to obtain a value expressed in hours and minutes.

For example for a bottling line which handles 320 bottles per minute (or 19,200 per hour) how long to process say 325,000 bottles in hours and minutes. I know that this will take 16.927 hours but would prefer the time expressed in hours and minutes.

If I have not expressed the problem clearly enough please accept my apologies and if anybody is able to help, I will be extremely grateful.

  MalcSP 12:04 03 Mar 2003

convert the answer to hours and minutesby using this format: =ROUND((calc-INT(calc))*60,0)/100+INT(calc)
where 'calc' is the formula to calculate the hours (16.927 in your example). It is cleaner to use a cell to do the calculation then another cell to do the conversion.

  GroupFC 13:03 03 Mar 2003

Thanks very much for your prompt response. I have tested that and it seems to work ( not that I doubted a response from the very knowledgeable people on this forum wouldn't solve the problem!).

I will leave this thread open a little longer to see if anybody else has any thing to offer!

  leo49 13:09 03 Mar 2003

This MS Knowledge Paper[214094] might come in handy in the future;

click here;EN-US;Q214094


  leo49 13:14 03 Mar 2003

Sorry MS link problems:

http ://support.microsoft.com/default.aspx?scid=kb;EN-US;Q214094

Take out the space after http


  GroupFC 13:24 03 Mar 2003

Thanks for that.

However I am fairly new to all this computing business, so please forgive me for asking what might seem an obvious question but..... the link you gave me goes to what I assume is the opening search page but you also gave what appears to be a specific article - the question is how do I get there?

  GroupFC 13:26 03 Mar 2003

That was quick!!

I'll try it now.

  leo49 13:33 03 Mar 2003

Just bung the Knowledge Paper number Q214094 in the Search[KB] box at the top left of the page if the 2nd link doesn't work.


  GroupFC 13:42 03 Mar 2003

Thanks for your response.

That seems to be what I want, but I must say I do find it quite hard getting my head round some of these microsoft articles.

Oh well I'll just have to keep trying (this is worse than being at school!).

  Ste R 62 11:30 04 Mar 2003

Try this:

If you have the number per min in cell A1 (=320)
the volume to get through in cell A2 (=325000)

In cell A3 input =A3/A1/60/24 (=0.7052...)

Then format cell A3 as time and your result will be displayed as hrs and mins. Ensure the format is [hh]:mm (Returns 16:55 in the case of the figures supplied)

A3/A1 converts the volume to time in minutes, dividing by 60 converts to hours and then dividing by 24 converts to days. Excel thinks of a day as equal to 1 so the dividing converts the data into a figure that excel can think of as a time. All that we do then is format the cell as time and it will return the data as required.

  GroupFC 22:31 04 Mar 2003

Thanks for that. That is exactly what the microsoft article that Leo49 referred me to said when I eventually managed to get my head round it, but not nearly so eloquently!!

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

iPhone X review

How to find a font: Discover the name of a typeface with these apps

The best iPhone for 2017

Comment créer un compte PayPal pour payer en ligne ?