SumProduct with date and time

[frogman7]

I have finally found that with my formulas the calulations are based
on the time. Can someone help me find the best way to correct this
without using macros to strip the time out of the data.

Bob 09/08/2007 01:05 13/08/2007 00:00 Bob
Bill 10/08/2007 23:39 Bill
Kyle 11/08/2007 07:36 Kyle
Bob 16/07/2007 20:39
Bill 27/07/2007 19:59
Kyle 18/07/2007 16:51
Bob 14/07/2007 16:31
Bill 21/06/2007 16:46
Kyle 30/06/2007 16:55
Bob 12/06/2007 01:05
Bill 13/06/2007 23:39 1 2 0 1 1
Kyle 14/06/2007 07:36 1 1 1 1 1
Bob 12/01/2007 20:39 1 1 2 0 1
Bill 13/08/2006 19:59
Kyle 14/05/1999 16:51

These are the formulas for the above calcs
SUMPRODUCT(--($A$1:$A$15$D$1),--($B$1:$B$15<$C$1),--($B$1:$B$15>$C
$1-15))
SUMPRODUCT(--($A$1:$A$15$D1),--($B$1:$B$15<$C$1-15),--($B$1:$B$15>$C
$1-30))
SUMPRODUCT(--($A$1:$A$15$D1),--($B$1:$B$15<$C$1-30),--($B$1:$B$15>$C
$1-60))
SUMPRODUCT(--($A$1:$A$15$D1),--($B$1:$B$15<$C$1-60),--($B$1:$B$15>$C
$1-90))
SUMPRODUCT(--($A$1:$A$15$D1),--($B$1:$B$15<$C$1-90))
SUMPRODUCT(--($A$1:$A$15$D$2),--($B$1:$B$15<$C$1),--($B$1:$B$15>$C
$1-15))
SUMPRODUCT(--($A$1:$A$15$D2),--($B$1:$B$15<$C$1-15),--($B$1:$B$15>$C
$1-30))
SUMPRODUCT(--($A$1:$A$15$D2),--($B$1:$B$15<$C$1-30),--($B$1:$B$15>$C
$1-60))
SUMPRODUCT(--($A$1:$A$15$D2),--($B$1:$B$15<$C$1-60),--($B$1:$B$15>$C
$1-90))
SUMPRODUCT(--($A$1:$A$15$D2),--($B$1:$B$15<$C$1-90))
SUMPRODUCT(--($A$1:$A$15$D$3),--($B$1:$B$15<$C$1),--($B$1:$B$15>$C
$1-15))
SUMPRODUCT(--($A$1:$A$15$D3),--($B$1:$B$15<$C$1-15),--($B$1:$B$15>$C
$1-30))
SUMPRODUCT(--($A$1:$A$15$D3),--($B$1:$B$15<$C$1-30),--($B$1:$B$15>$C
$1-60))
SUMPRODUCT(--($A$1:$A$15$D3),--($B$1:$B$15<$C$1-60),--($B$1:$B$15>$C
$1-90))
SUMPRODUCT(--($A$1:$A$15$D3),--($B$1:$B$15<$C$1-90))




Bob 09/08/2007 13/08/2007 Bob
Bill 10/08/2007 Bill
Kyle 11/08/2007 Kyle
Bob 16/07/2007
Bill 27/07/2007
Kyle 18/07/2007
Bob 14/07/2007
Bill 21/06/2007
Kyle 30/06/2007
Bob 12/06/2007
Bill 13/06/2007 1 1 1 1 1
Kyle 14/06/2007 1 1 1 1 1
Bob 12/01/2007 1 1 1 1 1
Bill 13/08/2006
Kyle 14/05/1999

These are the formulas for the above calcs
SUMPRODUCT(--($A$25:$A$39$D$25),--($B$25:$B$39<$C$25),--($B$25:$B$39>$C
$25-15))
SUMPRODUCT(--($A$25:$A$39$D25),--($B$25:$B$39<$C$25-15),--($B$25:$B$39>
$C$25-30))
SUMPRODUCT(--($A$25:$A$39$D25),--($B$25:$B$39<$C$25-30),--($B$25:$B$39>
$C$25-60))
SUMPRODUCT(--($A$25:$A$39$D25),--($B$25:$B$39<$C$25-60),--($B$25:$B$39>
$C$25-90))
SUMPRODUCT(--($A$25:$A$39$D25),--($B$25:$B$39<$C$25-90))
SUMPRODUCT(--($A$25:$A$39$D$26),--($B$25:$B$39<$C$25),--($B$25:$B$39>$C
$25-15))
SUMPRODUCT(--($A$25:$A$39$D26),--($B$25:$B$39<$C$25-15),--($B$25:$B$39>
$C$25-30))
SUMPRODUCT(--($A$25:$A$39$D26),--($B$25:$B$39<$C$25-30),--($B$25:$B$39>
$C$25-60))
SUMPRODUCT(--($A$25:$A$39$D26),--($B$25:$B$39<$C$25-60),--($B$25:$B$39>
$C$25-90))
SUMPRODUCT(--($A$25:$A$39$D26),--($B$25:$B$39<$C$25-90))
SUMPRODUCT(--($A$25:$A$39$D$27),--($B$25:$B$39<$C$25),--($B$25:$B$39>$C
$25-15))
SUMPRODUCT(--($A$25:$A$39$D27),--($B$25:$B$39<$C$25-15),--($B$25:$B$39>
$C$25-30))
SUMPRODUCT(--($A$25:$A$39$D27),--($B$25:$B$39<$C$25-30),--($B$25:$B$39>
$C$25-60))
SUMPRODUCT(--($A$25:$A$39$D27),--($B$25:$B$39<$C$25-60),--($B$25:$B$39>
$C$25-90))
SUMPRODUCT(--($A$25:$A$39$D27),--($B$25:$B$39<$C$25-90))

 

[Peo Sjoblom]

If you want the dates only from a range where there are both dates and times


--(INT($B$1:$B$15)<$C$1)

will strip off the time


to strip off the dates to compare times only


--(MOD($B$1:$B$15,1)<$C$1)





--
Regards,

Peo Sjoblom

 

[frogman7]

If you want the dates only from a range where there are both dates and times

--(INT($B$1:$B$15)<$C$1)

will strip off the time

to strip off the dates to compare times only

--(MOD($B$1:$B$15,1)<$C$1)

--
Regards,

Peo Sjoblom










- Show quoted text -

It work but when i apply it to my real data on a seprate sheet i get a
value error.
=SUMPRODUCT(--(INT('All Open USA Assigned'!$D$1:$D$9999)=$A45),--
(INT('All Open USA Assigned'!$AW$1:$AW$9999)>$B$1-15),--(INT('All Open
USA Assigned'!$AW$1:$AW$9999)<=$B$1))
What is wrong with this formula?

 

[Dave Peterson]

Do you have any text in D19999 or AW1:AW9999.

=int(somecellwithtext)
will return that #value! error

Do you have any errors (#value!'s) in any of those cells? Those errors will
make this formula return an error, too.

 

[frogman7]

Do you have any text in D19999 or AW1:AW9999.

=int(somecellwithtext)
will return that #value! error

Do you have any errors (#value!'s) in any of those cells? Those errors will
make this formula return an error, too.



frogman7 wrote:

That was it i had the header row in the formula.