Minutes function

[Ka Kwok]

Checking Google, this seems to be a common question but I
haven't quite found the answer I'm after.

My scenario is this. I'm trying to creat a function where
I'm calculating the number of hours taken on calls of
which there are two types.

I need to multiply the number of calls recorded and
multiply that with the average time per call. I then need
to repeat this process and get a sum of the two. A human
being can do this easy but I need it on a spreadsheet.
Anyway, I've worked it out before but for the life of me
can't remember how I did it.

So I need some help please.

I'll provide an example of the issue I have:

#Type A calls taken = 38
Avg time of Type A call = 2:20 (mm:ss)
Total time of Type A = 1:23 (hh:mm)

#Type B calls taken = 45
Avg time of Type B call = 2:20 (mm:ss)
Total time of Type B = 1:39 (hh:mm)

Sum (A and B) = 3 hours 2 minutes.

So how do I convert that into a spreadsheet function is
all I'm saying.

The problem is that a sum of the two as is, you end up
with something like 2:62 (hh:mm) which is correct but in
human terms is crap (pardon the fench).

Pls help.

Kind regards,

Ka.

 

[Bob Phillips]

Ka,

As long as all of the cells are formatted as time, 1:23 and 1:39 will sum
as 3:02, not 2:62.

Check your cells are all setup as time.

 

[Niek Otten]

Hi Ka,

Visit

http://www.cpearson.com/excel/datetime.htm#AddingTimes

to learn all about adding times

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Ka Khiong Kwok]

Mate, thanks for the repsonse.
My heads pretty messed up last night so I forgot to add
that part of the issue is that I'm multiplying an int with
a time and the result stinks.

I I mulplied 54 to 2:23 (2 minutes 23 seconds) I get 8:42
which really means nothing at all.

I'm starting to a better picture of where I fok'ed up
however if I could rack your brain a bit more, I'd
appreciate it.

Thanks again and have a great weekend.

Regards,

Ka.

 

[Bob Phillips]

Ka,

Again, that should not cause a problem,.

I think that you must have your result cell formatted as mm:ss to get 08:42.
Format it as hh:mm:ss (or better as [h]:mm:ss in case it exceeds 24 hours)
and you should be okay.