If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Breaking down a cell to create a function
I'm trying to create a flow graph by using current information on my
spreadsheet. I have a column of dates in this format 01/05/06. I trying to find a formula that will only use the year in that format, so I can show trends by the year. Please help! I've been trying this for a week and can't figure it out. And I don't even know if it's possible. |
#2
|
|||
|
|||
Breaking down a cell to create a function
Hi,
You don't actually say what you want to do but this takes the year from dates in column A and compares them to the year of a date in C1 and if the same sums column B =SUMPRODUCT((YEAR(A1:A13)=YEAR(C1))*(B1:B13)) Mike "Robert" wrote: I'm trying to create a flow graph by using current information on my spreadsheet. I have a column of dates in this format 01/05/06. I trying to find a formula that will only use the year in that format, so I can show trends by the year. Please help! I've been trying this for a week and can't figure it out. And I don't even know if it's possible. |
#3
|
|||
|
|||
Breaking down a cell to create a function
=YEAR(A1)
"Robert" wrote: I'm trying to create a flow graph by using current information on my spreadsheet. I have a column of dates in this format 01/05/06. I trying to find a formula that will only use the year in that format, so I can show trends by the year. Please help! I've been trying this for a week and can't figure it out. And I don't even know if it's possible. |
#4
|
|||
|
|||
Breaking down a cell to create a function
I have a column of over 700 of multiple dates in this format mm/dd/yy ie
6/15/07. I want to count all the years. Like 05, 06, 07, 08, 09 and create a flow chart from the with in the information. My columns looks something like this: 8/9/06 7/16/09 6/15/05 1/12/08 1/26/07 6/1/08 I want a function that only recognizes the year in that format, so I can count how many 05's, 06's, 07's, 08's I have so I can chart the different trends in years. Thanks for the help. "Mike H" wrote: Hi, You don't actually say what you want to do but this takes the year from dates in column A and compares them to the year of a date in C1 and if the same sums column B =SUMPRODUCT((YEAR(A1:A13)=YEAR(C1))*(B1:B13)) Mike "Robert" wrote: I'm trying to create a flow graph by using current information on my spreadsheet. I have a column of dates in this format 01/05/06. I trying to find a formula that will only use the year in that format, so I can show trends by the year. Please help! I've been trying this for a week and can't figure it out. And I don't even know if it's possible. |
#5
|
|||
|
|||
Breaking down a cell to create a function
=sumproduct(--(year(a1:a100)=2000))
(four digit years!) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Robert wrote: I have a column of over 700 of multiple dates in this format mm/dd/yy ie 6/15/07. I want to count all the years. Like 05, 06, 07, 08, 09 and create a flow chart from the with in the information. My columns looks something like this: 8/9/06 7/16/09 6/15/05 1/12/08 1/26/07 6/1/08 I want a function that only recognizes the year in that format, so I can count how many 05's, 06's, 07's, 08's I have so I can chart the different trends in years. Thanks for the help. "Mike H" wrote: Hi, You don't actually say what you want to do but this takes the year from dates in column A and compares them to the year of a date in C1 and if the same sums column B =SUMPRODUCT((YEAR(A1:A13)=YEAR(C1))*(B1:B13)) Mike "Robert" wrote: I'm trying to create a flow graph by using current information on my spreadsheet. I have a column of dates in this format 01/05/06. I trying to find a formula that will only use the year in that format, so I can show trends by the year. Please help! I've been trying this for a week and can't figure it out. And I don't even know if it's possible. -- Dave Peterson |
#6
|
|||
|
|||
Breaking down a cell to create a function
Assuming your data from a2:a7, criteria in start in b2
In C2: =SUMPRODUCT(--(TEXT($A$2:$A$7,"yy")=B2)) copy down "Robert" wrote: I have a column of over 700 of multiple dates in this format mm/dd/yy ie 6/15/07. I want to count all the years. Like 05, 06, 07, 08, 09 and create a flow chart from the with in the information. My columns looks something like this: 8/9/06 7/16/09 6/15/05 1/12/08 1/26/07 6/1/08 I want a function that only recognizes the year in that format, so I can count how many 05's, 06's, 07's, 08's I have so I can chart the different trends in years. Thanks for the help. "Mike H" wrote: Hi, You don't actually say what you want to do but this takes the year from dates in column A and compares them to the year of a date in C1 and if the same sums column B =SUMPRODUCT((YEAR(A1:A13)=YEAR(C1))*(B1:B13)) Mike "Robert" wrote: I'm trying to create a flow graph by using current information on my spreadsheet. I have a column of dates in this format 01/05/06. I trying to find a formula that will only use the year in that format, so I can show trends by the year. Please help! I've been trying this for a week and can't figure it out. And I don't even know if it's possible. |
Thread Tools | |
Display Modes | |
|
|