|
Replies: 16
| visibility 1
|
All-TigerNet [13190]
TigerPulse: 98%
Posts: 19419
Joined: 9/27/04
|
Excel help desperately needed
Jun 5, 2013, 1:47 PM
|
|
I'm looking at an excel sheet with 5 days worth of data. I need to separate out the data from 8:30am to 11:30am every day. The time stamp looks like this: 2012-10-09 08:30:00.000. Does anyone know how to sort out the times. I'm having to do it manually and it's killing me. If anyone can help me solve this problem (if there is a solution) I will be forever grateful.
|
|
|
|
 |
CU Medallion [54759]
TigerPulse: 100%
Posts: 52803
Joined: 10/1/03
|
get in line. We're working on ceiling fans.
Jun 5, 2013, 1:50 PM
|
|
well...ceiling fan posts
|
|
|
|
|
|
Oculus Spirit [75890]
TigerPulse: 100%
Posts: 108928
Joined: 10/26/03
|
What happens when you go to sort and
Jun 5, 2013, 1:52 PM
|
|
tell it to sort by the column with the time stamps?
|
|
|
|
|
|
All-TigerNet [13190]
TigerPulse: 98%
Posts: 19419
Joined: 9/27/04
|
It just sorts it by the day
Jun 5, 2013, 1:53 PM
|
|
Which doesn't help.
|
|
|
|
|
|
Oculus Spirit [75890]
TigerPulse: 100%
Posts: 108928
Joined: 10/26/03
|
Yep, date is in a ###### place.
Jun 5, 2013, 1:55 PM
|
|
I'd sort them all by date, then find the times you need and paste them into another tab.
|
|
|
|
|
|
All-TigerNet [12066]
TigerPulse: 83%
Posts: 13285
Joined: 1/29/09
|
Is the time stamp all in one cell?
Jun 5, 2013, 1:52 PM
|
|
You can use "Text to Columns" under the data tab to separate the time from the date and then sort by times.
|
|
|
|
|
|
All-TigerNet [12066]
TigerPulse: 83%
Posts: 13285
Joined: 1/29/09
|
Helpful note
Jun 5, 2013, 1:54 PM
|
|
Make sure there is a blank column to the right of the time before doing this or it will over write whatever is there.
|
|
|
|
|
|
Varsity [236]
TigerPulse: 100%
Posts: 500
Joined: 7/27/04
|
Re: Excel help desperately needed
Jun 5, 2013, 1:55 PM
|
|
Are you trying to sort first by time, then by day? If so, I would do 2 things:
1. Add column to the right with the formula "=RIGHT(A1,11)" Leave out the quotations and the A1 should be whatever cell the time stamp is in
2. Add another column with the formula "=LEFT(A1,10)" Again, leave out the quotations and the A1 should be whatever cell the time stamp is in
This will break up the contents and allow you to sort by time only, date only, time then date, or date then time.
Finally, and most importantly, take hammer and smash your hard drive to pieces.
|
|
|
|
|
|
All-TigerNet [13190]
TigerPulse: 98%
Posts: 19419
Joined: 9/27/04
|
One Billion thanks to everyone.
Jun 5, 2013, 1:58 PM
|
|
Job just got much easier.
|
|
|
|
|
|
Oculus Spirit [75890]
TigerPulse: 100%
Posts: 108928
Joined: 10/26/03
|
|
|
|
|
|
Rock Defender [54]
TigerPulse: 90%
Posts: 35
Joined: 11/30/98
|
soccrcrzy is I think a resident excel helpdesk*****
Jun 5, 2013, 1:58 PM
|
|
|
|
|
|
|
|
All-In [29046]
TigerPulse: 100%
Posts: 36106
Joined: 8/28/00
|
One time thing?
Jun 5, 2013, 2:02 PM
|
|
Sort all the data on time stamp column. Go through and remove all entries not during those times.
If its worth doing formulas and assumin date is in A1, you can use "=mod(A1,1)" and it will return just time portion. You can then sort on that field and delete those that don't belong.
|
|
|
|
|
|
All-In [29046]
TigerPulse: 100%
Posts: 36106
Joined: 8/28/00
|
Of course that is assuming your field isn't plain text
Jun 5, 2013, 2:04 PM
|
|
If it is then simply use a mid function to scrap the time information out of text field.
|
|
|
|
|
|
All-TigerNet [13190]
TigerPulse: 98%
Posts: 19419
Joined: 9/27/04
|
The main problem I'm running into with all soultions
Jun 5, 2013, 2:14 PM
|
|
I have to do this on around 600 separate files, so adding columns are formulas to each file probably takes longer than just going through and deleting the stuff I don't need using shift pgdown and control -. It's not hard but it's killing my eyes.
|
|
|
|
|
|
All-TigerNet [12066]
TigerPulse: 83%
Posts: 13285
Joined: 1/29/09
|
Your job sucks
Jun 5, 2013, 2:25 PM
|
|
HTH
|
|
|
|
|
|
All-In [29046]
TigerPulse: 100%
Posts: 36106
Joined: 8/28/00
|
|
|
|
|
|
All-In [30522]
TigerPulse: 100%
Posts: 62666
Joined: 10/4/99
|
Will this help the Barves win?***
Jun 5, 2013, 2:23 PM
|
|
|
|
|
|
|
|
Replies: 16
| visibility 1
|
|
|
50
