Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract Year Only
Hi,
I found a post that gave year function (=year(cell)) to extract only the year from a date. I have 1/15/2006 and I get 6/28/1905. This date was exported from Access into an Excel spreadsheet. I'm not sure if that would have an effect on it, but thought I would include it in message. If I format the new column to a number, I get 38722. I know Access uses numbers for dates, so I need to somehow convert to get correct year. Can anyone help me get 2006 from 1/15/2006? Thanks in advance for any help, Pam |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract Year Only
Make sure you format the cell with the =year() formula as General.
Right now you have it formatted as a date. Pam wrote: Hi, I found a post that gave year function (=year(cell)) to extract only the year from a date. I have 1/15/2006 and I get 6/28/1905. This date was exported from Access into an Excel spreadsheet. I'm not sure if that would have an effect on it, but thought I would include it in message. If I format the new column to a number, I get 38722. I know Access uses numbers for dates, so I need to somehow convert to get correct year. Can anyone help me get 2006 from 1/15/2006? Thanks in advance for any help, Pam -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract Year Only
Hi
with your date in a1 put this in b1 =A1 and a custom format of yyyy Mike "Pam" wrote: Hi, I found a post that gave year function (=year(cell)) to extract only the year from a date. I have 1/15/2006 and I get 6/28/1905. This date was exported from Access into an Excel spreadsheet. I'm not sure if that would have an effect on it, but thought I would include it in message. If I format the new column to a number, I get 38722. I know Access uses numbers for dates, so I need to somehow convert to get correct year. Can anyone help me get 2006 from 1/15/2006? Thanks in advance for any help, Pam |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract Year Only
I don't know what I was doing wrong, but I thought yyyy would work and it
didn't, but entered exactly as you have and it did. Thanks for the quick reply and helpful answer. "Mike H" wrote in message ... Hi with your date in a1 put this in b1 =A1 and a custom format of yyyy Mike "Pam" wrote: Hi, I found a post that gave year function (=year(cell)) to extract only the year from a date. I have 1/15/2006 and I get 6/28/1905. This date was exported from Access into an Excel spreadsheet. I'm not sure if that would have an effect on it, but thought I would include it in message. If I format the new column to a number, I get 38722. I know Access uses numbers for dates, so I need to somehow convert to get correct year. Can anyone help me get 2006 from 1/15/2006? Thanks in advance for any help, Pam |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract Year Only
Thank you for your quick reply and helpful answer.
"Dave Peterson" wrote in message ... Make sure you format the cell with the =year() formula as General. Right now you have it formatted as a date. Pam wrote: Hi, I found a post that gave year function (=year(cell)) to extract only the year from a date. I have 1/15/2006 and I get 6/28/1905. This date was exported from Access into an Excel spreadsheet. I'm not sure if that would have an effect on it, but thought I would include it in message. If I format the new column to a number, I get 38722. I know Access uses numbers for dates, so I need to somehow convert to get correct year. Can anyone help me get 2006 from 1/15/2006? Thanks in advance for any help, Pam -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract Year Only
Dates in Excel are stored as the number of days since 0-Jan-1900 (1 =
1-Jan-1900 through 39738 = 17-Oct-2008 and so on). When you use YEAR, you return to the cell the year as an integer, say 2006. If you format that cell as a date, Excel treats the value as a serial date, and 2006 days since 0-Jan-1900 is 28-June-1905. You need to format that cells as General or numeric, not as a date. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 17 Oct 2008 16:20:41 -0500, "Pam" wrote: Hi, I found a post that gave year function (=year(cell)) to extract only the year from a date. I have 1/15/2006 and I get 6/28/1905. This date was exported from Access into an Excel spreadsheet. I'm not sure if that would have an effect on it, but thought I would include it in message. If I format the new column to a number, I get 38722. I know Access uses numbers for dates, so I need to somehow convert to get correct year. Can anyone help me get 2006 from 1/15/2006? Thanks in advance for any help, Pam |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combination Graph with current year and prior year sales | Charts and Charting in Excel | |||
extract year from the date in a cell | Excel Worksheet Functions | |||
extract year | Excel Worksheet Functions | |||
Extract Unique Values, Then Extract Again to Remove Suffixes | Excel Discussion (Misc queries) | |||
How to compare current year to prior year in bar chart? | Charts and Charting in Excel |