- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - Microsoft
- >
- Re: How to copy and paste minutes into Excel
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-08-2006 12:49 PM
тАО06-08-2006 12:49 PM
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-08-2006 02:39 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-08-2006 02:45 PM
тАО06-08-2006 02:45 PM
Re: How to copy and paste minutes into 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
HP Master ASE, Storage, Servers, and Clustering
MCSE (NT 4.0, W2K, W2K3)
VCP (ESX2, Vi3, vSphere4, vSphere5, vSphere 6.x)
RHCE
NPP3 (Nutanix Platform Professional)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-08-2006 02:53 PM
тАО06-08-2006 02:53 PM
Re: How to copy and paste minutes into Excel
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-08-2006 02:54 PM
тАО06-08-2006 02:54 PM
Re: How to copy and paste minutes into Excel
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-08-2006 05:24 PM
тАО06-08-2006 05:24 PM
Re: How to copy and paste minutes into Excel
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
HP Master ASE, Storage, Servers, and Clustering
MCSE (NT 4.0, W2K, W2K3)
VCP (ESX2, Vi3, vSphere4, vSphere5, vSphere 6.x)
RHCE
NPP3 (Nutanix Platform Professional)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-09-2006 01:25 AM
тАО06-09-2006 01:25 AM
Re: How to copy and paste minutes into Excel
Thanks...Geoff