Calculating Hours and Minutes

[David]

I'm using Office 2003. I have a spreadsheet that calculates hours and
minutes. The formula I use is =INT(SUM(C5:E5)/0.6)+MOD(SUM(C5:E5),0.6)

The only issue I have is if I put in the values 1.20 (1 hour and 20 minutes)
it calculates as 2 hours. I need it to calculate exact hours and minutes (ie
70 minutes needs to calculate as 1 hour and 10 minutes)

Please Help!

Email reply to: (e-mail address removed)

Thank you very much.

 

[Paul Ballou]

http://www.cpearson.com/excel/topic.htm
Try the links here for help
--
Paul Ballou
MVP Office
http://office.microsoft.com/home
http://www.freeserifsoftware.com/
http://www.ballousgiftshop.com

 

[Helpful One]

I'm using Office 2003. I have a spreadsheet that
calculates hours and
minutes. The formula I use is
=INT(SUM(C5:E5)/0.6)+MOD(SUM(C5:E5),0.6)

Before commenting, let me make an attempt to define what I
think you are trying to do. It wasn't entirely clear.

You want to get the total hours and minutes expended (or
whatever) in three events for which you know the number of
minutes, or hours and minutes of each. What you do with
the results is not presented in your post.

If the above isn't correct, then you probably should ignore
the following.

Here are my comments and proposed solution:

The use of 1.20 to represent 1 hour and 20 minutes is pretty
unconventional. Doesn't mean it can't be handled but you
will need to utilize a couple of more rows and a more
complex set of forumlas. Rather than use an unconvential
approach, why not utilize two rows: one for hours and one
for minutes. Say rows C5:E5 for hours and C6:E6 for
minutes. Then for each event you can make your entries as
displayed below as follows:
Enter your 1 hour in the hour row, and your 20 minutes in
the corresponding minute row in the same column.
Enter your 70 minutes in the minute row.

Then sum both columns as follows:
For hours: SUM(C5:E5)+INT(SUM(C6:E6)/60)
For minutes: MOD(SUM(C6:E6),60)

What prevents this approach from working for you?

Your formula just doesn't work as you intended. Even if
1.20 represents in your mind 1 hour and 20 minutes, to the
formula it is 1.2 divided by .6 or 2 and there is no
remainder for the second part of the formula. Also, what
does the .6 represent?