How to copy and paste minutes into Excel

[Geoff Wild]

Okay - Unix guy here - so be nice.

What I'm trying to do, is take the details of an online cell phone bill and add up daytime minutes.

So, I select the info, copy into excel, and Excel auto formats the time field - incorrectly.

1:00 becomes 1:00:00 AM

and so on.

I right click and try to change the format - custom, mm:ss, number, what ever - and it displays everything from 00:00 to .041666.

The only way I was able to do this - vi

cat bill |awk '{print $11}' |awk -F: '{print $1}'

Here are the minutes:

1:00
1:00
1:00
3:00
1:00
1:00
1:00
1:00
1:00
1:00
3:00
2:00
1:00
1:00
2:00
2:00
1:00
1:00
1:00
4:00
14:00
1:00
1:00
5:00
4:00
16:00
3:00
1:00
5:00
3:00
1:00
1:00
1:00
3:00
1:00
1:00
1:00
2:00
1:00
2:00
2:00
1:00
1:00
1:00
1:00
2:00
1:00
1:00
1:00
1:00
2:00
1:00
5:00
2:00
2:00
1:00
1:00
1:00
1:00
1:00
1:00
1:00
1:00
1:00
1:00
1:00
2:00
1:00
1:00
1:00
1:00
1:00
1:00
2:00
29:00
1:00
1:00
4:00
2:00
1:00
1:00
1:00
2:00
1:00
1:00
1:00
1:00
1:00
1:00
2:00
1:00
2:00
1:00
1:00
3:00
1:00
1:00
10:00
1:00
1:00
6:00
25:00
10:00
2:00
8:00
1:00
2:00
6:00
1:00
2:00
1:00
2:00
1:00
1:00
2:00
5:00

I want to cut and paste that into Excel (so someone who doesn't know vi and Unix can do it) and total the minutes.

HOW? Why is Excel 2003 SP2 so difficult???

Argh...

Thanks...Geoff

[Phil.Howell · Accepted solution]

This is somewhat bizarre but...

In Excel - Tools, Options, International
Change the decimal sepatator to ":"

Paste your data, use auto-sum, voila

Phil

[Steven Clementi]

Now Phil is someone who knows Excel.


I was going to suggest that you paste into a txt file, then import it and use the : as the Delimiter to filter out the :00 from the first cell.. then autosum.

Phils's good, but I wonder if there is another way.. less intrusive to the excel config. (I will be playing with it all night.)


Steven

[Geoff Wild]

Well - Phil's worked - and it is bizarre -I'll leave this open for a bit to see any other solutions :)

Still like to know why "Format Cells" -> "Number Tab" -> "Custom" -> "mm:ss" doesn't do what I want....even when I set that column ahead of time...

Rgds...Geoff

[Phil.Howell]

The config change could be done in a macro,
and then un-done at the end?
The less intrusive way would be to paste into notepad and replace ":" with "." before pasting to excel.
This solution relies on the seconds being zero, as in the original.
Phil

[Steven Clementi]

Which is usually how a cell phone usage report comes... rounded up to the minute.



The problem is, and you probably figured this out already... is that if you set the cell as a Time format, even on custom.. setting to mm:ss is a Time format... and you then past a number in...

1:00
12:00

even though you mean minutes, it still considers the number before the : as the hour. Then the SUM() will add hours and you will get a bum number, not what you are expecting.

If you insert a 00: before each number

sample...

00:1:00
00:2:00
00:1:00
00:2:00
00:1:00
00:1:00
00:2:00
00:5:00

and sum() it, you will get (or should get) the right calculation. If you go over 60 minutes, you will get the sum in hh:mm:ss which you can then convert into mm:ss through some calculation or function.


Steven

[Geoff Wild]

Phil and Steven's are both a good work around.

Thanks...Geoff