Over time speadsheet

  Tulips 18:00 24 Jan 09
Locked

in column H I have the hours and in column I there is the minutes, starting in H6 to H30 I have put the "sum" formula in and the same in the I column, is there a way when the minutes gets greater than 60 minutes, that it can change the hours accordingly. Clear as mud, eh.

  VoG II 18:30 24 Jan 09

It is much easier to work with times in Excel if you input them as Excel times e.g. hh:mm.

However, we can get around that given that you've entered the data in the way that you have (assuming that you only have whole numbers, no decimals).

In a spare column (say J) in J6 enter the formula

=TIME(H6,I6,0)

and fill the formula down as far as needed.

Then to get the total duration use the formula

=SUM(J6:J30)

and format the cell containing that formula as Custom [h]:mm

  Tulips 18:53 24 Jan 09

If I was starting from scratch what would you suggest because what you have told me to do has confused the heck out of me.

  VoG II 19:00 24 Jan 09

What I would do is choose a single column to enter times and use hh:mm.

For example type in 11:25 for 11 hours 25 minutes.

Then use a SUM() on that single column. You will still need to use a Custom format of [h]:mm in the cell containing the SUM() formula to get the total duration displayed if it exceeds 24 hours.

  Tulips 19:33 24 Jan 09

Why is the solution always under your nose and always so simple, cheers VoG.

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

Sniper Elite 4 review: Headshotting Nazis has never felt so good

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

The Best Design, Illustration, Animation and VFX Awards of 2017

WWDC 2017 dates: How to get WWDC 2017 tickets, when is WWDC 2017 and more details announced