test if more than one year

[Djblois]

I was wondering if it is at all possible to test for multiple years in two different ways

1) If I have multiple dates in a column I would like to test if there are multiple years (ex: 2005 and 2006)
2) If I use the dates in a pivottable I would like to test the pivottable column field of date for more than one year. Same as above

[Ken Puls]

I'm not sure on the Pivot Table portion, but for the first one, I would think a formula could do it. Say you have your dates in cells A1:A100:

=if(year(max(A1:A100))<>year(min(A1:A100)),"Multiple years","Same year")

HTH,

[Zack Barresse]

If your data is in column A and they are all dates, you could use something like this...


=IF(SUMPRODUCT(--(YEAR(A2:A10)=2006))=COUNT(A2:A10),"Yes","No")


Of course you would need to define the year (2006 in this example) and it assumes a range of only A2:A10.

Is the year defined here, or are you looking for any difference?

[Bob Phillips]

=IF(SUMPRODUCT(--(YEAR(A2:A10)=2006))=COUNT(A2:A10),"Yes","No")

Of course you would need to define the year (2006 in this example) and it assumes a range of only A2:A10.

Is the year defined here, or are you looking for any difference?

Use the year of the first cell in the range

=IF(SUMPRODUCT(--(YEAR(A2:A10)=YEAR(A2)))=COUNT(A2:A10),"Yes","No")

[Djblois]

Can I use that formula in VBA?

[Ken Puls]

Sure

[vba]Range("B10").Formula = " =IF(SUMPRODUCT(--(YEAR(A2:A10)=YEAR(A2)))=COUNT(A2:A10),""Yes"",""No"")"[/vba]

You may want to go to FormulaR1C1 if you want to set the ranges via code. In that case it would look like (for the same thing):
[vba]Range("B10").Formula = "=IF(SUMPRODUCT(--(YEAR(R[-8]C[-1]:RC[-1])=YEAR(R[-8]C[-1])))=COUNT(R[-8]C[-1]:RC[-1]),""Yes"",""No"")"
[/vba]

HTH,

[Zack Barresse]

I had thought of that Bob, but alas did not use it as the OP was (I didn't think) very specific. Of course MSFT still did not put in an ISDATE() function, which totally baffles my mind.

[Bob Phillips]

I had thought of that Bob, but alas did not use it as the OP was (I didn't think) very specific. Of course MSFT still did not put in an ISDATE() function, which totally baffles my mind.

Aah, I see what you mean. He could have meant multiple year dates, of soecifically 2005 and 2006, rather than multiple year dates.

[Zack Barresse]

Exactly. Ambiguity at its best.

[Bob Phillips]

If it were the former, what should be done if there were 2004 and 2006? Don't answer, I am sure it doesn't matter.

[Ken Puls]

I just assumed returning a cell value that said "multiple years" vs "single year" and figured the op would adjust as necessary.

[Bob Phillips]

So did I, but you know that Zack is a bit odd!

[Ken Puls]

Well, no argument there.

[Djblois]

Sure

[vba]Range("B10").Formula = " =IF(SUMPRODUCT(--(YEAR(A2:A10)=YEAR(A2)))=COUNT(A2:A10),""Yes"",""No"")"[/vba]

You may want to go to FormulaR1C1 if you want to set the ranges via code. In that case it would look like (for the same thing):
[vba]Range("B10").Formula = "=IF(SUMPRODUCT(--(YEAR(R[-8]C[-1]:RC[-1])=YEAR(R[-8]C[-1])))=COUNT(R[-8]C[-1]:RC[-1]),""Yes"",""No"")"
[/vba]

HTH,

I like this it seems it will do what I am looking for. However is it possible, to have adjust to the size of the column. Since the column will always be different lengths.

[Ken Puls]

Sure can. Can you tell us what column your data is in, and what row it starts in?

[Ken Puls]

Nevermind,

Again, assuming column A with data starting in row 2:

[vba]With ActiveSheet
.Range("B10").Formula = _
"=IF(SUMPRODUCT(--(YEAR(A2:A" & .Cells(.Rows.Count, 1).End(xlUp).Row & _
")=YEAR(A2)))=COUNT(A2:A" & .Cells(.Rows.Count, 1).End(xlUp).Row & "),""Yes"",""No"")"
End With[/vba]

[Zack Barresse]

So did I, but you know that Zack is a bit odd!

Hmm, could you elaborate a little on that Bob? You lost me at Zack...

[Ken Puls]

Ehrm... he said odd, not dense.

[Djblois]

Would this work as a VBA statement? I am trying to use it to say if more than 1 year then group the date by year. The rest I already have, I just need to figure out this last piece of the puzzle.

[vba]IF(SUMPRODUCT(--(YEAR(A2:A" & .Cells(.Rows.Count, 1).End(xlUp).Row & ")=YEAR(A2)))
_ =COUNT(A2:A" & .Cells(.Rows.Count, 1).End(xlUp).Row &") > 1 then[/vba]

[Zack Barresse]

It should, as long as you have it in a With statement that contains the sheet, as that is how you have it referenced. The logic is sufficient though. I know a good way to find out... test it!