Using time formats in minute units

[josh]

Hey guys I have a problem

I download call information into excel to make pivot reports.

One of the fields is call duration. The format of the information is
Minutes:seconds
i.e. the call lasted 5:35 = 5 minutes and 35 seconds. Excel understand this
as 5:35AM. This becomes even more problematic when I try and calculate total
minutes, as excel sees it as 5:35AM + 12:02PM ....

How can I make excel see this information as minutes and seconds and not
"time"
I've tried to convert the cells into text but all that does is give me the
excel code version of time.

This is an example of a line
Date Time Origin Destination Duration
10-Sep 14:53 *** *** 08:51 -------> 8:51:00AM


Thank you
Josh

 

[Ron Rosenfeld]

Hey guys I have a problem

I download call information into excel to make pivot reports.

One of the fields is call duration. The format of the information is
Minutes:seconds
i.e. the call lasted 5:35 = 5 minutes and 35 seconds. Excel understand this
as 5:35AM. This becomes even more problematic when I try and calculate total
minutes, as excel sees it as 5:35AM + 12:02PM ....

How can I make excel see this information as minutes and seconds and not
"time"
I've tried to convert the cells into text but all that does is give me the
excel code version of time.

This is an example of a line
Date Time Origin Destination Duration
10-Sep 14:53 *** *** 08:51 -------> 8:51:00AM


Thank you
Josh

To convert Hours:Minutes to Minutes:Seconds, divide the value by 60; format
appropriately. For example -- [m]:ss or [h]:mm:ss


--ron

 

[josh]

Unfortunately that does not work. I am looking for a way to put the format
into minutes and seconds so that i can add them together ot make a total
time. The way you suggested, would still be an AM/PM Time, which cannot be
added.

Hey guys I have a problem

I download call information into excel to make pivot reports.

One of the fields is call duration. The format of the information is
Minutes:seconds
i.e. the call lasted 5:35 = 5 minutes and 35 seconds. Excel understand this
as 5:35AM. This becomes even more problematic when I try and calculate total
minutes, as excel sees it as 5:35AM + 12:02PM ....

How can I make excel see this information as minutes and seconds and not
"time"
I've tried to convert the cells into text but all that does is give me the
excel code version of time.

This is an example of a line
Date Time Origin Destination Duration
10-Sep 14:53 *** *** 08:51 -------> 8:51:00AM


Thank you
Josh

To convert Hours:Minutes to Minutes:Seconds, divide the value by 60; format
appropriately. For example -- [m]:ss or [h]:mm:ss


--ron

 

[Arvi Laanemets]

Hi

Forget about AM/PM, it's for display only. Your problem is, that Excel
interprets 5:35 as 5 hours and 35 minutes - it always excepts the time in
format h:mm.ss

My advice is to convert all imported times to excel time format - divide
them all with 60. To do so, enter the number 60 into some free cell, copy
it, select the whole range with imported times, select PasteSpecial, and in
PasteSpecial window, check Divide. Press OK. Scrap the number 60, and format
all times as h:mm.ss

Next problem will be with summing times. It's easy to solve - format all
cells with sums as Custom "[h]:mm:ss" or "[m]:ss", which will prevent
'rollover' at 24 hours or 60 minutes.

Arvi Laanemets

 

[Ron Rosenfeld]

Unfortunately that does not work. I am looking for a way to put the format
into minutes and seconds so that i can add them together ot make a total
time. The way you suggested, would still be an AM/PM Time, which cannot be
added.

Yes it does work. What you are not understanding is that what you see in the
formula bar is irrelevant to what you want to do.

Did you format the cells as I suggested?

Did you try to add the values?

If you do what I wrote, and then format the cells as I suggested, you will
certainly be able to add them together. If you cannot, you are doing something
else incorrect.


--ron