Solved Convert date to yyyy

November 25, 2009 at 09:08:27
Specs: Windows XP

Dear Gentleperson:

I have exported data -- dates in particular -- from a source that has the date format as M/D/yyyy (e.g.. 11/12/1987) as shown in the formula bar; however, in the column it shows 12-Nov-1987. I have 900+ rows of data and I need to illustrate, for example, how many of X were completed in each year and/or each month in a particular year. So, I am assuming I need to create a "month" column and a "year" column. I am new to Excel and imagined that date conversion and formatting to "yyyy" would be simple, but Excel does not offer the "yyyy" in the "custom" formatting field. It was told that I have to change to text, add various columns, and paste special values over formulas, but I have NO idea on how to do any of that.

Any assistance or direction is greatly appreciated. Thank you.

Adam


See More: Convert date to yyyy

Report •


#1
November 25, 2009 at 10:30:27

If cell A1 has your full date 11/12/1987
Then in cell B1 enter =DAY(A1)
Then in cell C1 enter =MONTH(A1)
Then in cell D1 enter =YEAR(A1)

Cell B1 should now equal 12
Cell C1 should now equal 11
Cell D1 should now equal 1987

MIKE

http://www.skeptic.com/


Report •

#2
November 25, 2009 at 10:44:37
✔ Best Answer

Hi,

First a quick background on dates in Excel.
Excel holds dates as numbers, starting with 1 for 01 January 1900
25 November 2009 is 40142

Excel also holds the time in the same number, but as the decimal part of the number, 12 noon is 0.5, i.e., half way through the day. Noon on 25 November is 40142.5

Excel allows you to format the date so that it shows in recognizable ways. A series of standard formats are available. In a cell with a date, right click and select Format cells, Numbers Tab - Date section.
However, there is also a Custom section in the Numbers Tab and you can create your own custom format, including "yyyy"

This will get the column of dates to show as year only e.g., 1985, but it does not change the underlying value in the cell. Enter 25-Nov-2009 in cell A1, then in Cell A2 enter A1+1 and it will show 26-Nov-2009.
Go to cell A1 and in the Numbers Tab, select Custom and enter yyyy in the Type: box.
The date in cell A2 doesn't change, showing that although cell A1 shows 2009, it still retains its original value of 40142. If it changed to 2009, A2 would be 2010 which in Excel dates was 02-July-1905 !

If you want to select cells that fall within a certain range you can create separate columns for years and months, using the formulas B1=Year(A1) and C1=Month(A1), but this gets complicated.

An easier way to do this is to create the date ranges you want and use formulas that act on the original date values.

Try this:
Enter these dates in column A starting at Cell A1

	A
1	25/Nov/2009
2	09/Aug/2009
3	14/Aug/2008
4	15/Aug/2008
5	11/Jun/2009
6	26/Apr/2009
7	20/Sep/2008
8	22/Aug/2007
9	02/Aug/2008

In cells E1 to F2 enter these dates:

	E		F
1	01-Aug-07	01-Aug-08
2	31-Aug-07	31-Aug-08

These make two ranges of dates: August in 2007 and August in 2008
In the cells underneath enter these formulas:
In cell E3
=SUMPRODUCT(($A$1:$A$9>=E1)*($A$1:$A$9<=E2))

In cell F3
=SUMPRODUCT(($A$1:$A$9>=F1)*($A$1:$A$9<=F2))

These formulas will give you the number of dates that fall into each range, as follows:

01-Aug-07	01-Aug-08
31-Aug-07	31-Aug-08
1		3

Hope this gives you enough information to get started with your solution.

Regards


Report •

#3
November 25, 2009 at 13:42:00

Mike & Humar:

Thank you very much for your immediate responses and for the information provided. I will apply both methods when I return home.

Have a great one!

Adam


Report •

Related Solutions


Ask Question