Hours and minutes in Excel

  Craig.m 08:10 AM 11 Jul 11

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 AM 11 Jul 11

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

  Zak 11:06 AM 11 Jul 11

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

  Zak 11:06 AM 11 Jul 11

  Zak 11:09 AM 11 Jul 11

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 AM 11 Jul 11

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 AM 11 Jul 11

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 PM 11 Jul 11

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.

Best Cyber Monday deals 2015 live blog: Best UK Cyber Monday tech deals, sales, discounts & offers

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

Harry Potter's graphic designers showcase magical props & posters in new exhibition

Apple rumours & predictions 2016: What to expect from Apple in 2016