Hours and minutes in Excel

  Craig.m 08:10 AM 11 Jul 11
Locked

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.

Advertisement

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

Sony Xperia Z5 review: Hands-on with the phone which the Z3+ should have been

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

Get your photos turned into illos by some of the world’s best artists

What to expect at the September Apple event: Apple announces 9 September event, sends out invites