Hi,

I want to record/count the number of times a date changes in any cell in a
column?

I will appreciate all your help.Please let me know.

Thanks!
NM

Try,

=SUMPRODUCT((A1:A100<"")*(ISNUMBER(A1:A100)/(COUNTIF(A1:A100,A1:A100&""))))

Mike

"NM" wrote:

Hi,

I want to record/count the number of times a date changes in any cell in a
column?

I will appreciate all your help.Please let me know.

Thanks!
NM

Question is a little unclear. I am interpreting your question rephrased as:
"I have a column with dates, and I want to know how many times the date
changes from row to row as I go down the column."
Assume your dates are in cells A2:A100, this formula, I believe, will give
you what you want:
=SUMPRODUCT(--($A$2:$A$99<$A$3:$A$100))
So say you had dates ranged A2:A7 as so:
10/17/08 | 10/17/08 | 10/18/08 | 10/19/08 | 10/19/08 | 10/20/08
Your result should be 3, as it changes from 10/17/08 to 10/18/08, then the
next cell to 10/19/08, and one more time to 10/20/08.
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"NM" wrote:

Hi,

I want to record/count the number of times a date changes in any cell in a
column?

I will appreciate all your help.Please let me know.

Thanks!
NM

Using a small Data Set of 5
10/17/08 | 10/17/08 | 10/17/08 | 10/17/08 | 10/17/08
Your formula results = 1, my formula results = 0

10/17/08 | 10/17/08 | 10/18/08 | 10/18/08 | 10/18/08
Your formula results = 2, my formula results = 1

10/17/08 | 10/17/08 | 10/18/08 | 10/18/08 | 10/17/08
Your formula results = 2, my formula results = 2

10/17/08 | 10/18/08 | 10/19/08 | 10/18/08 | 10/17/08
Your formula results = 3, my formula results = 4
--
** John C **



"Mike H" wrote:

Try,

=SUMPRODUCT((A1:A100<"")*(ISNUMBER(A1:A100)/(COUNTIF(A1:A100,A1:A100&""))))

Mike

"NM" wrote:

Hi,

I want to record/count the number of times a date changes in any cell in a
column?

I will appreciate all your help.Please let me know.

Thanks!
NM

Hi,

My formula does exactly what I wanted it to do. My understanding of the OP
requirement is different to yours, I'm looking for the number of unique dates.

Mike

"John C" wrote:

Using a small Data Set of 5
10/17/08 | 10/17/08 | 10/17/08 | 10/17/08 | 10/17/08
Your formula results = 1, my formula results = 0

10/17/08 | 10/17/08 | 10/18/08 | 10/18/08 | 10/18/08
Your formula results = 2, my formula results = 1

10/17/08 | 10/17/08 | 10/18/08 | 10/18/08 | 10/17/08
Your formula results = 2, my formula results = 2

10/17/08 | 10/18/08 | 10/19/08 | 10/18/08 | 10/17/08
Your formula results = 3, my formula results = 4
--
** John C **



"Mike H" wrote:

Try,

=SUMPRODUCT((A1:A100<"")*(ISNUMBER(A1:A100)/(COUNTIF(A1:A100,A1:A100&""))))

Mike

"NM" wrote:

Hi,

I want to record/count the number of times a date changes in any cell in a
column?

I will appreciate all your help.Please let me know.

Thanks!
NM

I did not say your formula was wrong. I was giving the OP data as it was
obvious that the OP's request is not quite clear. You interpreted the OP
wanted number of unique dates. I interpreted that the OP wanted to know how
many times the date changed. I was giving the OP as much info as possible,
as, like I said, the OP's original request is ambiguous.
--
** John C **

"Mike H" wrote:

Hi,

My formula does exactly what I wanted it to do. My understanding of the OP
requirement is different to yours, I'm looking for the number of unique dates.

Mike

"John C" wrote:

Using a small Data Set of 5
10/17/08 | 10/17/08 | 10/17/08 | 10/17/08 | 10/17/08
Your formula results = 1, my formula results = 0

10/17/08 | 10/17/08 | 10/18/08 | 10/18/08 | 10/18/08
Your formula results = 2, my formula results = 1

10/17/08 | 10/17/08 | 10/18/08 | 10/18/08 | 10/17/08
Your formula results = 2, my formula results = 2

10/17/08 | 10/18/08 | 10/19/08 | 10/18/08 | 10/17/08
Your formula results = 3, my formula results = 4
--
** John C **



"Mike H" wrote:

Try,

=SUMPRODUCT((A1:A100<"")*(ISNUMBER(A1:A100)/(COUNTIF(A1:A100,A1:A100&""))))

Mike

"NM" wrote:

Hi,

I want to record/count the number of times a date changes in any cell in a
column?

I will appreciate all your help.Please let me know.

Thanks!
NM

Thanks much for your responses.
Understanding that my question was a little unclear. I want to provide some
input to clarify it.
I have 120 rows of different Projects with 16 columns of dates. In a column
I want to find out how many times date changes in a single cell.Say, A2 was
12/10/08 and it changed to 12/20, so it should be counted as one.
Similarly if A3 changes from 12/10 to 12/1, it should be counted and the
total change in the column would be 2.
Let me know if I'm still unclear.

Thanks!




"John C" wrote:

Question is a little unclear. I am interpreting your question rephrased as:
"I have a column with dates, and I want to know how many times the date
changes from row to row as I go down the column."
Assume your dates are in cells A2:A100, this formula, I believe, will give
you what you want:
=SUMPRODUCT(--($A$2:$A$99<$A$3:$A$100))
So say you had dates ranged A2:A7 as so:
10/17/08 | 10/17/08 | 10/18/08 | 10/19/08 | 10/19/08 | 10/20/08
Your result should be 3, as it changes from 10/17/08 to 10/18/08, then the
next cell to 10/19/08, and one more time to 10/20/08.
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"NM" wrote:

Hi,

I want to record/count the number of times a date changes in any cell in a
column?

I will appreciate all your help.Please let me know.

Thanks!
NM

If you are saying that you want to type a new date into a cell that has an
existing date, and then count that as a new entry, you are entering either
ground for circular calculations/iterations, or VBA territory. Either way it
is going to get tricky.

There might be a different/better way without entering into that kind of
stuff, perhaps if you detailed a little more about what you are trying to
capture.
--
** John C **

"NM" wrote:

Thanks much for your responses.
Understanding that my question was a little unclear. I want to provide some
input to clarify it.
I have 120 rows of different Projects with 16 columns of dates. In a column
I want to find out how many times date changes in a single cell.Say, A2 was
12/10/08 and it changed to 12/20, so it should be counted as one.
Similarly if A3 changes from 12/10 to 12/1, it should be counted and the
total change in the column would be 2.
Let me know if I'm still unclear.

Thanks!




"John C" wrote:

Question is a little unclear. I am interpreting your question rephrased as:
"I have a column with dates, and I want to know how many times the date
changes from row to row as I go down the column."
Assume your dates are in cells A2:A100, this formula, I believe, will give
you what you want:
=SUMPRODUCT(--($A$2:$A$99<$A$3:$A$100))
So say you had dates ranged A2:A7 as so:
10/17/08 | 10/17/08 | 10/18/08 | 10/19/08 | 10/19/08 | 10/20/08
Your result should be 3, as it changes from 10/17/08 to 10/18/08, then the
next cell to 10/19/08, and one more time to 10/20/08.
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"NM" wrote:

Hi,

I want to record/count the number of times a date changes in any cell in a
column?

I will appreciate all your help.Please let me know.

Thanks!
NM

Hi,

That's what I thought when I read the question!

1. You can turn on Track Changes. Look this topic up in the help system for
details, it may be overkill.
2. You can write VBA code to do this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("A1:A10"))
If Not isect Is Nothing Then
Target.Offset(0, 5) = Target.Offset(0, 5) + 1
End If
End Sub

This goes into the Worksheet object for the sheet you are working with.

This code would check everytime a change was made to the range A1:A10 and
increase the number in the cell 5 columns to the right by 1.

If this helps, please click the Yes button
--
Thanks,
Shane Devenshire


"NM" wrote:

Thanks much for your responses.
Understanding that my question was a little unclear. I want to provide some
input to clarify it.
I have 120 rows of different Projects with 16 columns of dates. In a column
I want to find out how many times date changes in a single cell.Say, A2 was
12/10/08 and it changed to 12/20, so it should be counted as one.
Similarly if A3 changes from 12/10 to 12/1, it should be counted and the
total change in the column would be 2.
Let me know if I'm still unclear.

Thanks!




"John C" wrote:

Question is a little unclear. I am interpreting your question rephrased as:
"I have a column with dates, and I want to know how many times the date
changes from row to row as I go down the column."
Assume your dates are in cells A2:A100, this formula, I believe, will give
you what you want:
=SUMPRODUCT(--($A$2:$A$99<$A$3:$A$100))
So say you had dates ranged A2:A7 as so:
10/17/08 | 10/17/08 | 10/18/08 | 10/19/08 | 10/19/08 | 10/20/08
Your result should be 3, as it changes from 10/17/08 to 10/18/08, then the
next cell to 10/19/08, and one more time to 10/20/08.
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"NM" wrote:

Hi,

I want to record/count the number of times a date changes in any cell in a
column?

I will appreciate all your help.Please let me know.

Thanks!
NM

Thanks for your reply.

I have 16 columns with dates and I want to count changes in each column
independent of another. Is it possible to do that?

Thanks!

"ShaneDevenshire" wrote:

Hi,

That's what I thought when I read the question!

1. You can turn on Track Changes. Look this topic up in the help system for
details, it may be overkill.
2. You can write VBA code to do this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("A1:A10"))
If Not isect Is Nothing Then
Target.Offset(0, 5) = Target.Offset(0, 5) + 1
End If
End Sub

This goes into the Worksheet object for the sheet you are working with.

This code would check everytime a change was made to the range A1:A10 and
increase the number in the cell 5 columns to the right by 1.

If this helps, please click the Yes button
--
Thanks,
Shane Devenshire


"NM" wrote:

Thanks much for your responses.
Understanding that my question was a little unclear. I want to provide some
input to clarify it.
I have 120 rows of different Projects with 16 columns of dates. In a column
I want to find out how many times date changes in a single cell.Say, A2 was
12/10/08 and it changed to 12/20, so it should be counted as one.
Similarly if A3 changes from 12/10 to 12/1, it should be counted and the
total change in the column would be 2.
Let me know if I'm still unclear.

Thanks!




"John C" wrote:

Question is a little unclear. I am interpreting your question rephrased as:
"I have a column with dates, and I want to know how many times the date
changes from row to row as I go down the column."
Assume your dates are in cells A2:A100, this formula, I believe, will give
you what you want:
=SUMPRODUCT(--($A$2:$A$99<$A$3:$A$100))
So say you had dates ranged A2:A7 as so:
10/17/08 | 10/17/08 | 10/18/08 | 10/19/08 | 10/19/08 | 10/20/08
Your result should be 3, as it changes from 10/17/08 to 10/18/08, then the
next cell to 10/19/08, and one more time to 10/20/08.
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"NM" wrote:

Hi,

I want to record/count the number of times a date changes in any cell in a
column?

I will appreciate all your help.Please let me know.

Thanks!
NM