Hours and minutes in Excel

  Craig.m 08:10 11 Jul 2011

I am using Excel to total hours and minutes on a piece or equipment, a record so we can see which ones are due service when. Easy enough to put in running time but if it is more than 24 hours or the total is greater than this it starts at zero again as it classes it as one day, I want to retain the value in hours which could run intot he hundreds but can't find a way of losing the 24 hour roll over element.

AWould anybody know how to format excel to count in hours and minutes but remove the day element?

  Zak 11:04 11 Jul 2011

Format cells as below and it should do what you want:

  Zak 11:06 11 Jul 2011

Don't know why but format formula did not get posted, here goes again:

  Zak 11:06 11 Jul 2011

  Zak 11:09 11 Jul 2011

I give up!! put square brackets either side of the 'h' in the format h:mm

You will see one in Excel custom format which includes seconds.

  wee eddie 11:44 11 Jul 2011

Write your Start and Finish Times over midnight, thus 16:00:00 and 03:15:00 and the Formula to calculate the hours between =MOD(B10-A10,1)

Many thanks to Vog on that one

  wee eddie 11:51 11 Jul 2011

I assume that, were the machine to work solidly for a week, or so, one would enter the Start and Finish times, as before, but replace the '1' with a '7' or however many days there were in between.

Never tried that one, as I'm calculating shifts that run past midnight.

  Craig.m 12:16 11 Jul 2011

Thanks Zak, once you got the message over I tried it and it does just what I was after.

Wee eddie, that would work to get the hours difference but it is a case of the register having hours and minutes put in but with no record of times. I am creating a sheet that is retrospective and so the times would not be available.

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

Elsewhere on IDG sites

iMac Pro review

Illustrator Charles Williams on how to create magazines and book covers

iMac Pro review

Les meilleures prises CPL (2018)