Excel 97 – Showing a value in hours and minutes

  GroupFC 11:36 03 Mar 03

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 03

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 03

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 03

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

click here;EN-US;Q214094


  leo49 13:14 03 Mar 03

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 03

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 03

That was quick!!

I'll try it now.

  leo49 13:33 03 Mar 03

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 03

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 03

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 03

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.

What is Amazon Go and will it come to the UK? The store without checkouts or queues

1995-2015: How technology has changed the world in 20 years

Hands-on with the Star Wars fighting drones you can fly yourself

iPhone 9 and beyond: 32 amazing future smartphone developments - graphene, supercapacitor…