Cleaning up timestamp information in Metafilter Infodump
February 26, 2015 12:15 PM Subscribe
Probably pretty simple, but I would like to clean up the Metafilter Infodump timestamp, help?
I am moderately ok in excel. Anyways, I am learning some data visualization techniques and would like to work with the Metafilter Infodump.
Currently, the timestamp looks like this: Dec 8 2003 01:28:06:877PM
Preferably I'd like:
Column 1: Dec 8 2003
Column 2: 01:28
Unfortunately I can't separate out the date and time as it is, my efforts to change the format to the timestamp columns don't do anything. Formulas that I have used to separate out these elements in the past don't seem to be working.
Ideally I'd like to be able to have a column with the date and a column with cleaned up time (hours, minutes) so that I can isolate data for day, month, year, time of day, etc. I can do that if I could just separate date and Hours/Minutes into their own columns.
Many thanks
I am moderately ok in excel. Anyways, I am learning some data visualization techniques and would like to work with the Metafilter Infodump.
Currently, the timestamp looks like this: Dec 8 2003 01:28:06:877PM
Preferably I'd like:
Column 1: Dec 8 2003
Column 2: 01:28
Unfortunately I can't separate out the date and time as it is, my efforts to change the format to the timestamp columns don't do anything. Formulas that I have used to separate out these elements in the past don't seem to be working.
Ideally I'd like to be able to have a column with the date and a column with cleaned up time (hours, minutes) so that I can isolate data for day, month, year, time of day, etc. I can do that if I could just separate date and Hours/Minutes into their own columns.
Many thanks
If I'm looking at the data correctly it looks like the date portion of the time stamp is a fixed width--the day takes up two characters even on single digit days. If that's really true you can first do text-to-columns with a fixed-width column to carve out the date and save yourself at least one step.
posted by sevenless at 12:30 PM on February 26, 2015 [1 favorite]
posted by sevenless at 12:30 PM on February 26, 2015 [1 favorite]
Do you really want 1pm to be parsed as just 1:00 or do you want it 13:00, or 1:00PM? Assuming you want to keep the AM PM designations, I'd start by replacing "PM" with " PM" and the same for AM.
posted by soelo at 12:51 PM on February 26, 2015 [1 favorite]
posted by soelo at 12:51 PM on February 26, 2015 [1 favorite]
Response by poster: soelo, I want it to be military time.
posted by Sreiny at 12:54 PM on February 26, 2015
posted by Sreiny at 12:54 PM on February 26, 2015
In that case, and I'm suggesting this without Excel in front of me so again there are definitely easier ways this is just off the top of my head, you could, after you have the time split into columns, columns, sort your columns so all the PMs are together and put C1+12 (or whatever cell number) for just those columns, copy/paste the result as values (alt+ctrl+v lets you paste special and you can pick values instead of pasting the formula back in), replace the hours with the new twenty-four hour value, and then concatenate. Again, I'm sure there are easier ways to do this but it seems like a pretty simple workaround.
posted by Mrs. Pterodactyl at 3:49 PM on February 26, 2015 [1 favorite]
posted by Mrs. Pterodactyl at 3:49 PM on February 26, 2015 [1 favorite]
This thread is closed to new comments.
For the second column, do text to columns separated by colons, then delete the seconds, pm, &c. and concatenate the last two with a colon. There are other ways to do this but this is probably the easiest.
posted by Mrs. Pterodactyl at 12:22 PM on February 26, 2015 [1 favorite]