General
cancel
Showing results for 
Search instead for 
Did you mean: 

How to copy and paste minutes into Excel

SOLVED
Go to solution
Geoff Wild
Honored Contributor

How to copy and paste minutes into Excel

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



Proverbs 3:5,6 Trust in the Lord with all your heart and lean not on your own understanding; in all your ways acknowledge him, and he will make all your paths straight.
6 REPLIES
Phil.Howell
Honored Contributor
Solution

Re: How to copy and paste minutes into Excel

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
Honored Contributor

Re: How to copy and paste minutes into Excel

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
Steven Clementi
HP Master ASE, Storage and Clustering
MCSE (NT 4.0, W2K, W2K3)
VCP (ESX2, Vi3, vSphere4, vSphere5)
RHCE
NPP3 (Nutanix Platform Professional)
Geoff Wild
Honored Contributor

Re: How to copy and paste minutes into Excel

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
Proverbs 3:5,6 Trust in the Lord with all your heart and lean not on your own understanding; in all your ways acknowledge him, and he will make all your paths straight.
Phil.Howell
Honored Contributor

Re: How to copy and paste minutes into Excel

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
Honored Contributor

Re: How to copy and paste minutes into Excel

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
Steven Clementi
HP Master ASE, Storage and Clustering
MCSE (NT 4.0, W2K, W2K3)
VCP (ESX2, Vi3, vSphere4, vSphere5)
RHCE
NPP3 (Nutanix Platform Professional)
Geoff Wild
Honored Contributor

Re: How to copy and paste minutes into Excel

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

Thanks...Geoff
Proverbs 3:5,6 Trust in the Lord with all your heart and lean not on your own understanding; in all your ways acknowledge him, and he will make all your paths straight.