Can I remove serial number information from dates in Excel?
July 8, 2009 5:04 PM Subscribe
I have an excel file with a column of dates, in the xx/xx/xxxx format. I would like to strip out the month and day information, so I could just have a column that shows the year, but excel's method of representing dates as serial numbers has foiled every attempt. Any ideas?
I have an excel file containing thousands of building permit records for about a 30-year period. There is a column listing the exact date of the permit issuance. I'd like to get rid of the month and day information, and just have a column of years, so I could sort the data by year only, regardless of the actual date in a given year. However, using formulas to strip off the month and day and copy the year into another column doesn't work as easily as I thought it would, because of Excel's serial number dating system.
I've tried reformatting the date cells to "text" or "general," and copying the information from the date column to another with a "paste special" of values only. Regardless, any formula I try acts on the underlying serial number information, not the date information as shown on screen.
I've used the google to no avail. I am also a relative Excel n00b, so I'm hoping there's some simple solution out there that I'm missing.
I have an excel file containing thousands of building permit records for about a 30-year period. There is a column listing the exact date of the permit issuance. I'd like to get rid of the month and day information, and just have a column of years, so I could sort the data by year only, regardless of the actual date in a given year. However, using formulas to strip off the month and day and copy the year into another column doesn't work as easily as I thought it would, because of Excel's serial number dating system.
I've tried reformatting the date cells to "text" or "general," and copying the information from the date column to another with a "paste special" of values only. Regardless, any formula I try acts on the underlying serial number information, not the date information as shown on screen.
I've used the google to no avail. I am also a relative Excel n00b, so I'm hoping there's some simple solution out there that I'm missing.
Response by poster: ...and, problem solved. I'm officially embarrassed about the time I and my colleagues wasted on this! Thanks.
posted by M.C. Lo-Carb! at 5:14 PM on July 8, 2009
posted by M.C. Lo-Carb! at 5:14 PM on July 8, 2009
make sure you have two empty columns next to your date column. Then click Data>Text to Columns. Select Delimited then next. Check the box for "other" and enter "/" then click next. Select each column and make sure the radio buttons are set to text for each column. Click Finished.
posted by Morgangr at 5:16 PM on July 8, 2009
posted by Morgangr at 5:16 PM on July 8, 2009
I would just:
Format
Cell
Number
Custom
YYYY
The date info is retained in the formula bar, but only the year shows and it seems to sort randomly (I mean that it disregards the month and day when sorting)
posted by SLC Mom at 6:00 PM on July 8, 2009
Format
Cell
Number
Custom
YYYY
The date info is retained in the formula bar, but only the year shows and it seems to sort randomly (I mean that it disregards the month and day when sorting)
posted by SLC Mom at 6:00 PM on July 8, 2009
Regardless, any formula I try acts on the underlying serial number information, not the date information as shown on screen.
Ooooooo. So my method is not the trick you need.
I withdraw from the field.
Damn. And just when I was feeling elegant.
posted by SLC Mom at 6:04 PM on July 8, 2009
Ooooooo. So my method is not the trick you need.
I withdraw from the field.
Damn. And just when I was feeling elegant.
posted by SLC Mom at 6:04 PM on July 8, 2009
If you use the formatting method described by SLC Mom above to show just the years, then copy the column of years and re-paste it back in using the "values only" option of Paste Special, the column will then consist of plain old four-digit numbers like 2009 as far as Excel is concerned, not dates anymore.
posted by letourneau at 4:25 AM on July 9, 2009
posted by letourneau at 4:25 AM on July 9, 2009
If you use the formatting method described by SLC Mom above to show just the years, then copy the column of years and re-paste it back in using the "values only" option of Paste Special, the column will then consist of plain old four-digit numbers like 2009 as far as Excel is concerned, not dates anymore.
This is incorrect. "Paste values" will take the result of a formula and paste just the result, not the formula, into the cell, but if it's just a matter of formatting (as it is here), the entire internally-represented value, regardless of how it's displayed, will be pasted.
posted by DevilsAdvocate at 5:21 AM on July 9, 2009
This is incorrect. "Paste values" will take the result of a formula and paste just the result, not the formula, into the cell, but if it's just a matter of formatting (as it is here), the entire internally-represented value, regardless of how it's displayed, will be pasted.
posted by DevilsAdvocate at 5:21 AM on July 9, 2009
Best answer: Obviously, pombe's solution is the way to go and the date functions like year, month, etc are the reason excel stores dates this way in the first place. But, if you DID want to convert the date value to text, you could use the text function like so (where A1 has your date):
=TEXT(A1,"MM/DD/YYYY")
The second argument will take any of the custom date-related formats that excel recognizes.
posted by yarrow at 9:40 AM on July 9, 2009
=TEXT(A1,"MM/DD/YYYY")
The second argument will take any of the custom date-related formats that excel recognizes.
posted by yarrow at 9:40 AM on July 9, 2009
« Older Query about Rock Band and GHWT controllers | Real life hobby for someone who enjoys building... Newer »
This thread is closed to new comments.
posted by pombe at 5:06 PM on July 8, 2009