Formula for cells containing last month's date
 

I have an Excel spreadsheet which includes a column containing dates.

I am trying to create a formula which will tell me the number of cells in
the column that have dates from last month. For some unknown reason I am
having great difficulty with this - even before trying to ensure the formula
will cope with the December/January issue, when the 'month-1' principle
presumably will not work.

Can anyone help please?

Thanks,

V

Formula for cells containing last month's date
 

assuming your dates are in A1:A7 and todays date is in cell C1, try:

=SUMPRODUCT(--(TEXT(A1:A7,"mmyy")=TEXT(C1-DAY(C1),"mmyy")))


"Victor Delta" wrote:

I have an Excel spreadsheet which includes a column containing dates.

I am trying to create a formula which will tell me the number of cells in
the column that have dates from last month. For some unknown reason I am
having great difficulty with this - even before trying to ensure the formula
will cope with the December/January issue, when the 'month-1' principle
presumably will not work.

Can anyone help please?

Thanks,

V

Formula for cells containing last month's date
 

"JMB" wrote in message
...
assuming your dates are in A1:A7 and todays date is in cell C1, try:

=SUMPRODUCT(--(TEXT(A1:A7,"mmyy")=TEXT(C1-DAY(C1),"mmyy")))

JMB

Many thanks. Works brilliantly!

However, the only thing I don't understand is the two hyphens at the start
of the first bracket?

Regards,

V

Formula for cells containing last month's date
 

"Victor Delta" wrote in message ...
"JMB" wrote in message
...
assuming your dates are in A1:A7 and todays date is in cell C1, try:

=SUMPRODUCT(--(TEXT(A1:A7,"mmyy")=TEXT(C1-DAY(C1),"mmyy")))

JMB

Many thanks. Works brilliantly!

However, the only thing I don't understand is the two hyphens at the start
of the first bracket?

Two minus at the front convert text to numeric, such as making a number text value negative
then positive.

Formula for cells containing last month's date
 

"David McRitchie" wrote in message
...
"Victor Delta" wrote in message
...
"JMB" wrote in message
...
assuming your dates are in A1:A7 and todays date is in cell C1, try:

=SUMPRODUCT(--(TEXT(A1:A7,"mmyy")=TEXT(C1-DAY(C1),"mmyy")))

JMB

Many thanks. Works brilliantly!

However, the only thing I don't understand is the two hyphens at the
start of the first bracket?

Two minus at the front convert text to numeric, such as making a number
text value negative
then positive.

Many thanks,

V

Formula for cells containing last month's date
 

David is correct, but the double minus also converts true/false values to 1
and 0 respectively. This allows you to sum the true/false values and get a
count of how many trues there were.

Bob Phillips has an excellent discussion on sumproduct here
http://xldynamic.com/source/xld.SUMPRODUCT.html

"Victor Delta" wrote:

"JMB" wrote in message
...
assuming your dates are in A1:A7 and todays date is in cell C1, try:

=SUMPRODUCT(--(TEXT(A1:A7,"mmyy")=TEXT(C1-DAY(C1),"mmyy")))

JMB

Many thanks. Works brilliantly!

However, the only thing I don't understand is the two hyphens at the start
of the first bracket?

Regards,

V

Formula for cells containing last month's date
 

"JMB" wrote in message
...
David is correct, but the double minus also converts true/false values to
1
and 0 respectively. This allows you to sum the true/false values and get
a
count of how many trues there were.

Bob Phillips has an excellent discussion on sumproduct here
http://xldynamic.com/source/xld.SUMPRODUCT.html

Thanks for that.

To be honest, I had not come across the Excel sumproduct function before. It
looks very powerful so I will enjoy reading this interesting article.

V