Trouble with pasting time-like format

[khandu]

Hey Guys

So I am trying to paste these in a cell

1:00
1:00
1:30
5:30
0:30
1:00
2:30


and then do an addition

but when I click on the cell, excel shows them on the formula bar as

1:00:00 AM
1:00:00 AM
1:30:00 AM
5:30:00 AM
12:30:00 AM
1:00:00 AM
2:30:00 AM

How to stop it.. I went to format cell and choose it as general.. still the same happens

Basically what I am trying to do is.. that I am trying to add my minutes in a mobile bill

So in my bill I get my minutes called in this format
1:00
1:00
1:30
5:30
0:30
1:00
2:30

So small numbers in the case above is not much of a problem and addition of all works fine

BUT

when the list is long.. it automagically adds wierd and gives my wrong output.. There I am thinking.. if excel stops recognizing it as a time in format above.. hopefully the addition of minutes (more than 60min) does not go haywire

hope i made myself clear

 

[mole999]

if you format as [mm]:ss and input your times properly that should work,

bear in mind a 1 is equal to a day

 

[khandu]

if you format as [mm]:ss and input your times properly that should work,

bear in mind a 1 is equal to a day

Ok its not working out for me..

so lets say I have this data

<table border="0" cellpadding="0" cellspacing="0" width="82"><col style="width: 62pt;" width="82"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 62pt;" align="right" height="20" width="82">1:00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" align="right" height="20">5:00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" align="right" height="20">1:00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" align="right" height="20">5:00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" align="right" height="20">1:30</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" align="right" height="20">0:30</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" align="right" height="20">0:30</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" align="right" height="20">0:30</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" align="right" height="20">4:00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" align="right" height="20">1:00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" align="right" height="20">11:00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" align="right" height="20">1:30</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" align="right" height="20">4:00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" align="right" height="20">11:30</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" align="right" height="20">1:00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" align="right" height="20">10:00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" align="right" height="20">0:30</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" align="right" height="20">1:00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" align="right" height="20">2:00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" align="right" height="20">0:30</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" align="right" height="20">1:00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" align="right" height="20">0:30</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" align="right" height="20">1:00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" align="right" height="20">0:30</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" align="right" height="20">3:00</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" align="right" height="20">1:30</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" align="right" height="20">2:30</td> </tr> </tbody></table>

How do I put it in excel and add so that I get total minutes.. I think I should get answer as 73

the data above is in minutes and I am using excel 2010

 

[khandu]

What is interesting to see is.. if i select all the data above in excel

on the bottom right corner.. the sum shows correct : 73

but my formula of sum.. just shows 1:00 (=SUM(H12:H38))

so maybe the formatting of that sum cell is also wrong.. but i m unable to get 73 there as a result

 

[Marcelo Branco]

Hi,

Excel is showing 1 because it is showimng the hour=73 (24+24+24+1)

So you have to divide the sum by 60 (you get 1:13) and format [mm]:ss to get 73:00

HTH

M.

 

[khandu]

Hi,

Excel is showing 1 because it is showimng the hour=73 (24+24+24+1)

So you have to divide the sum by 60 (you get 1:13) and format [mm]:ss to get 73:00

HTH

M.

Awesome.. thanks working now.. what will i do without this forum :D

 

[Marcelo Branco]

Explaining better. Excel is showing the Time that corresponds to 73 hours, its 1:00 o'clock or 1AM

If the sum would be 25 or 49 the result also would be 1:00 (the Time corresponding to 25hours or 49 hours)

M.