uk date comparisons

[markjerz]

Hi,
I'm really struggling to get the following thing working, basically
because nothing seems to like uk date formats.

Anyway, I have a column with dates in (uk format!), called finish, and
another column with numbers (time in days) in.

I want to calculate the number of rows with a finish date in a certain
month and a time of less than 25 (days). The finish date may be blank,
in which case I want to ignore these.

Currently I have ( as one of my many possibilities):

=SUM(($W$24:$W$33024<>"")*(DATEDIF($W$24:$W
$33024,DATE(YEAR(TODAY()),MONTH(TODAY())-3,1),"m")=0)*($AU$24:$AU
$33024<=25)*1)

The 3 is just an example of the number of months I want to go back
from today

Thanks for any help.

 

[Bob Phillips]

=SUMPRODUCT(--($W$24:$W$33024<>""),--(TEXT($W$24:$W$33024,"mmm-yyyy")="Nov-2006"),--($AA$24:$AA$33024<=25))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Mark,

Try this :
=SUMPRODUCT(
--(TEXT($W$24:$W$33024,"MM.YYYY")=TEXT(TODAY(),"MM.YYYY")),
--($AU$24:$AU$33024<=25),
--(ISNUMBER($AU$24:$AU$33024))
)

No need to check for blank dates as this uses the text date.
But need to check for blank days.

 

[markjerz]

Mark,

Try this :
=SUMPRODUCT(
--(TEXT($W$24:$W$33024,"MM.YYYY")=TEXT(TODAY(),"MM.YYYY")),
--($AU$24:$AU$33024<=25),
--(ISNUMBER($AU$24:$AU$33024))
)

No need to check for blank dates as this uses the text date.
But need to check for blank days.

Can I just ask why you have "--" in this formula?

 

[Guest]

it causes a true/false value to be changed to a 1/0 value since sumproduct
only works on arrays of numbers.

so for each row, if one of the conditions is not met, i results in a zero.
When you multiply across all conditions, if any condition is not met, the
product of that row is zero. For rows where all conditions are met, the
results of the multiplication of the conditions is 1 mutiplied against the
value of interest. The net results are that only the values of interest
which meet all conditions are included in the sum.

 

[Dave Peterson]

Some more resources:

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[markjerz]

Some more resources:

Bob Phillips explains =sumproduct() in much more detail here:http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:http://mcgimpsey.com/excel/formulae/doubleneg.html

Thanks for all of your help guys.

Works a treat!