Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to Graph Comparisons | Charts and Charting in Excel | |||
Date comparisons within AND() functions | Excel Worksheet Functions | |||
Yr to Yr comparisons | Excel Discussion (Misc queries) | |||
Formula For Date Comparisons | Excel Discussion (Misc queries) | |||
Automated Year-to-Date Comparisons | Excel Programming |