Over time speadsheet

  Tulips 18:00 24 Jan 2009
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 2009

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 2009

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 2009

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 2009

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.

Elsewhere on IDG sites

What is ransomware and how do I protect my PC from Petya?

Illustrator Mariana Rodrigues' beautiful flora and fauna for Bombay Sapphire gin

How to remove Mac ransomware

Comment mettre à jour Kodi ?