Converting Minutes:Seconds to Minutes

  • Thread starter Thread starter NickC
  • Start date Start date
N

NickC

I could download my telephone bill online.
When I view the bill online The Duration of Call column which is listed in
minutes appears as (for example)
5:00 ..............(for 5 minutes)
16:00.............(for 16 minutes)
21:00.............(for 21 minutes)
29:00.............(for 29 minutes)
32:00.............(for 32 minutes)

When I copy or download the bill this column appears as (Same example)
5:00................(for 5 minutes)
16:00.............(for 16 minutes)
29:00:00.............(for 29 minutes)
32:00:00.............(for 32 minutes)

I would like to be able to do a total for the number of minutes for over 500
calls listed in my bill. Do I need to change the format of the cells. When
I look in the formulas bar, the 29:00:00 (29 minutes), I see 01-01-1900
5:00:00 AM

Thanks for help
 
How do calls greater than 1 hour look?

If you know you have none greater than 1 hour, you could in B1 use

=MOD(A3,1)

and sum these.

doesn't the telephone company sum them for you?
 
Greater than one hour e.g 94:00:00 minutes
and in the formula bar: 03-01-1900 10:00:00 PM
 
Nick,

Try this formula in B1 and copy down, then sum the B's

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,":",""))=1,TIME(0,LEFT(A1,LEN(A1)-3),RIGHT(A1,
2)),TIME(LEFT(A1,LEN(A1)-3),MID(A1,LEN(A1)-3,2),RIGHT(A1,2)))
 
I could download my telephone bill online.
When I view the bill online The Duration of Call column which is listed in
minutes appears as (for example)
5:00 ..............(for 5 minutes)
16:00.............(for 16 minutes)
21:00.............(for 21 minutes)
29:00.............(for 29 minutes)
32:00.............(for 32 minutes)

When I copy or download the bill this column appears as (Same example)
5:00................(for 5 minutes)
16:00.............(for 16 minutes)
29:00:00.............(for 29 minutes)
32:00:00.............(for 32 minutes)

I would like to be able to do a total for the number of minutes for over 500
calls listed in my bill. Do I need to change the format of the cells. When
I look in the formulas bar, the 29:00:00 (29 minutes), I see 01-01-1900
5:00:00 AM
Click to expand...

Strangely, the following format seems to work: [h]:ss; it gives:
5:00
16:00
21:00
29:00
32:00
which Excel adds up to 103:00

I suspect Excel treats the digits to the left of the colon as hours but,
because of the square brackets, doesn't aggregate them into days; the
digits to the right of the colon are then treated as minutes, which
doesn't matter as both seconds and minutes have 60 in every major unit
(sexagesimal system).

To convert those 103 minutes to Hours:minutes divide the cell by 60 and
display the result in the same format ([h]:ss).
 
Hi Nick,

After getting the values into excel, the data seems to be formatted as
dates, and minutes as hours. Do the following:

Assuming your minutes data is in A1 and down, enter the following
formula in column B and copy down:
=HOUR(A1)+DAY(A1)*24

And then sum them using sum
=SUM(B1:B10)

Your example below gave me 82 minutes by using the above method.

Mangesh
 
That way would convert the 94:00:00 example he gave wrongly, the OP stated
that was 1 hour 34 minutes in reality.

--
HTH

Bob Phillips

"mangesh_yadav" <[email protected]>
wrote in message
 
Hi Bob,

What my formula does is convert the OP's readings into integer minutes. For
instance, 94:00:00 is converted to 94. All the results are integers. The sum
will be total minutes.

If the OP further wants to convert this into actual time, then he could use
something like:
=TIME(0,B1,0)
where B1 holds the result of the previous formula, or the result of the sum
of all such numbers.
94 would give him 1:34:00


By the way, your formula gave me 4:07:07 for the 94:00:00 reading.


Mangesh





Bob Phillips said:
That way would convert the 94:00:00 example he gave wrongly, the OP stated
that was 1 hour 34 minutes in reality.

--
HTH

Bob Phillips

"mangesh_yadav"
Click to expand...
wrote in message
Click to expand...
 
Back
Top