using sumproduct with dates

[frogman7]

I am trying to get counts by name for all the ticket open from now
until 15 days ago then 16 to 30 days ago etc.

Name date and time
Johnson 08/08/2007 00:37
Jones 09/08/2007 02:20
Smith 09/08/2007 22:17
Smith 07/08/2007 20:33
Doe 19/07/2007 22:52
Michaels 09/08/2007 15:44
Michaels 09/08/2007 15:51
Doe 09/08/2007 21:43
Doe 13/07/2007 19:39
Doe 19/07/2007 19:43
Doe 19/07/2007 22:22
Doe 13/07/2007 20:31
Doe 13/07/2007 19:51
Russell 09/08/2007 21:08
Doe 18/07/2007 18:28
Russell 04/08/2007 00:34
Doe 25/07/2007 20:12
Smith 06/08/2007 20:55
Doe 30/07/2007 22:31

I want to use the current date to find all the Doe tickets that are
between:
now to 15 days ago
16 to 30 days ago
31 to 60 days ago
61 to 90 days ago
90+ days ago

 

[frogman7]

I am trying to get counts by name for all the ticket open from now
until 15 days ago then 16 to 30 days ago etc.

Name date and time
Johnson 08/08/2007 00:37
Jones 09/08/2007 02:20
Smith 09/08/2007 22:17
Smith 07/08/2007 20:33
Doe 19/07/2007 22:52
Michaels 09/08/2007 15:44
Michaels 09/08/2007 15:51
Doe 09/08/2007 21:43
Doe 13/07/2007 19:39
Doe 19/07/2007 19:43
Doe 19/07/2007 22:22
Doe 13/07/2007 20:31
Doe 13/07/2007 19:51
Russell 09/08/2007 21:08
Doe 18/07/2007 18:28
Russell 04/08/2007 00:34
Doe 25/07/2007 20:12
Smith 06/08/2007 20:55
Doe 30/07/2007 22:31

I want to use the current date to find all the Doe tickets that are
between:
now to 15 days ago
16 to 30 days ago
31 to 60 days ago
61 to 90 days ago
90+ days ago

This is what I have so far but it gives me a #VALUE error
=SUMPRODUCT(--(TEXT(B1:B99,"dd/mm/yyyy HH:mm")>=TEXT(NOW()-15,"dd/mm/
yyyy HH:mm")),--(A1:A99="Doe"))

 

[Peo Sjoblom]

1. You make it awfully hard to help you by hard coding UK formats in your
formula,


If indeed the values in B are genuine dates and times you can simply use


=SUMPRODUCT(--(A1:A99="Doe"),--(B1:B99>=NOW()-15))

Of course if the dates are text you will get TRUE for all dates for Doe
you could also make sure there are no dates in the future

=SUMPRODUCT(--(A1:A99="Doe"),--(B1:B99>=NOW()-15),--(B1:B99<=NOW()))


--
Regards,

Peo Sjoblom

 

[frogman7]

1. You make it awfully hard to help you by hard coding UK formats in your
formula,

If indeed the values in B are genuine dates and times you can simply use

=SUMPRODUCT(--(A1:A99="Doe"),--(B1:B99>=NOW()-15))

Of course if the dates are text you will get TRUE for all dates for Doe
you could also make sure there are no dates in the future

=SUMPRODUCT(--(A1:A99="Doe"),--(B1:B99>=NOW()-15),--(B1:B99<=NOW()))

--
Regards,

Peo Sjoblom







- Show quoted text -

thanks that worked great