uk date comparisons
 

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.

uk date comparisons
 

=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)



wrote in message
oups.com...
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.

uk date comparisons
 

Mark,

Try this :
=SUMPRODUCT(
--(TEXT($W$24:$W$33024,"MM.YYYY")=TEXT(TODAY(),"MM.Y YYY")),
--($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.


--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


" wrote:

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.

uk date comparisons
 

On Feb 19, 12:41 pm, Martin Fishlock
wrote:
Mark,

Try this :
=SUMPRODUCT(
--(TEXT($W$24:$W$33024,"MM.YYYY")=TEXT(TODAY(),"MM.Y YYY")),
--($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.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

" wrote:
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.

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

uk date comparisons
 

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.

--
Regards.

" wrote:

On Feb 19, 12:41 pm, Martin Fishlock
wrote:
Mark,

Try this :
=SUMPRODUCT(
--(TEXT($W$24:$W$33024,"MM.YYYY")=TEXT(TODAY(),"MM.Y YYY")),
--($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.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

" wrote:
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.

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

uk date comparisons
 

Some more resources:

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

" wrote:

On Feb 19, 12:41 pm, Martin Fishlock
wrote:
Mark,

Try this :
=SUMPRODUCT(
--(TEXT($W$24:$W$33024,"MM.YYYY")=TEXT(TODAY(),"MM.Y YYY")),
--($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.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

" wrote:
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.

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

--

Dave Peterson

uk date comparisons
 

On Feb 19, 2:35 pm, Dave Peterson wrote:
Some more resources:

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

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



" wrote:

On Feb 19, 12:41 pm, Martin Fishlock
wrote:
Mark,

Try this :
=SUMPRODUCT(
--(TEXT($W$24:$W$33024,"MM.YYYY")=TEXT(TODAY(),"MM.Y YYY")),
--($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.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

" wrote:
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.

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

--

Dave Peterson

Thanks for all of your help guys.

Works a treat!