Home |
Search |
Today's Posts |
#1
|
|||
|
|||
working with dates
How can I write this expression so that my answer is not #VALUE?
I'm working with dates, and I want to count only the dates in columns H, J, & L if they fall between the dates entered in F1 and H1. IF H5 or J5 or L5 is F1 and <=H1 count as 1. |
#2
|
|||
|
|||
Hi!
IF H5 or J5 or L5 is F1 and <=H1 count as 1. So, does that mean if all 3 cells meet the criteria it still counts as 1? F1 = lower boundry date H1 = upper boundry date =IF(SUMPRODUCT(--(MOD(COLUMN(H5:L5),2)=0),--(H5:L5=F1),--(H5:L5<=H1)),1,0) Biff "Pe66les" wrote in message ... How can I write this expression so that my answer is not #VALUE? I'm working with dates, and I want to count only the dates in columns H, J, & L if they fall between the dates entered in F1 and H1. IF H5 or J5 or L5 is F1 and <=H1 count as 1. |
#3
|
|||
|
|||
On Tue, 23 Aug 2005 17:50:03 -0700, "Pe66les"
wrote: How can I write this expression so that my answer is not #VALUE? I'm working with dates, and I want to count only the dates in columns H, J, & L if they fall between the dates entered in F1 and H1. IF H5 or J5 or L5 is F1 and <=H1 count as 1. I'm not sure if you want to count each qualifying date as 1 (so your result range would be 0 to 3) or count 1 if any of the dates qualify (so your result range would be 0 to 1) For the former: =AND(H5F1,H5<=H1)+AND(J5F1,J5<=H1)+AND(L5F1,L5< =H1) For the latter: =--OR(AND(H5F1,H5<=H1)+AND(J5F1,J5<=H1)+AND(L5F1,L 5<=H1)) --ron |
#4
|
|||
|
|||
Yes, if any or all 3 cells meet the criteria it only counts as 1.
"Biff" wrote: Hi! IF H5 or J5 or L5 is F1 and <=H1 count as 1. So, does that mean if all 3 cells meet the criteria it still counts as 1? F1 = lower boundry date H1 = upper boundry date =IF(SUMPRODUCT(--(MOD(COLUMN(H5:L5),2)=0),--(H5:L5=F1),--(H5:L5<=H1)),1,0) Biff "Pe66les" wrote in message ... How can I write this expression so that my answer is not #VALUE? I'm working with dates, and I want to count only the dates in columns H, J, & L if they fall between the dates entered in F1 and H1. IF H5 or J5 or L5 is F1 and <=H1 count as 1. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to find the working days difference between to dates? | Excel Discussion (Misc queries) | |||
working with dates | Excel Discussion (Misc queries) | |||
working with dates in fiscal years | Excel Worksheet Functions | |||
Is there a way to calculate business working days between dates i. | Excel Worksheet Functions | |||
Formating Dates for production schedule | Excel Discussion (Misc queries) |