adding / converting minutes to hours & minutes

[Keith Nichols]

Hi,

I am trying to sum time in minutes and display the result as hh:mm but getting all sorts of problems as the minutes always seem to be related to a date rather than just numbers. Here is the sort of thing I want:

Time Day
00:55 Mon
00:25 Tue
01:20 Wed
00:30 Thur
00:30 Fri

02:40 Total


I have managed to convert the total minutes to hours & minutes but cannot concatenate the results to display time corectly. If the minutes are less than 10 then there is no leading zero or if the minutes add a number ending in zero this is not displayed either.

Any ideas?

 

[HaHoBe]

Hi, Keith,

any good reason not to use the normal time segment with an input of 00:00 which should convert properly if you format the result user defined by [hh]:mm for more than 24 hours?

I put another way to solve the problem in the attachment but sadly enough I always get 03:40 as a my result.

Ciao,
Holger

 

[Keith Nichols]

Marvelous

HoHeBo,

Thanks, works a treat.

The times I'm recording are my exercise time and the actual start finish is irrelevant, just the duration is of interest. This is also what comes of my Polar heart rate recorder watch and to use time, I would have to work it out every time. Not a big deal but not what I actually wanted to do.

Your solution is good and I don't think I would have stumbled accross it on my own. It is sometimes surprising how convoluted things must get to do something apparaently simple in Excel.

One thing I am not understanding: After extracting the minutes from the formatted hours & minutes and summing them you use

=(INT(F8/60) & MOD(F8,60))*1

to reconvert it to hours and minutes in the cell formatted 00":"00. What I don't get is the *1 at the end. It doesn't work without this but what does multiplying by 1 do to the formula?

Anyway, many thanks again.

Regards,

Keith.

 

[HaHoBe]

Hi, Keith,

the & will concatenate numbers to a string thus a multiplication has to be fulfilled to convert the string back into numbers which may be displayed as wanted.

Ciao,
Holger

 

[Keith Nichols]

Thanks Holger.

 

[Keith Nichols]

Small problem with times over 1 hour

Hi Holger,

The formula
=(INT(G87/60) & MOD(G87,60))*1
displays the correct formatted results as long as the minutes do not fall in the ranges 60-69, 120-129, 180-189, etc. ie. when the modulus is under 10.

To get round this problem I have added a logical test and multuipled the integer value by 10 if the modulus is under 10.

=((IF(MOD(G87,60)<10,(INT(G87/60)*10),INT(G87/60))) & MOD(G87,60))*1

Makes it a bit cumbersome, but it works.

Regards,


Keith