IF statement. Dates with different outcomes

Microsoft Excel 2003 (full product)
April 7, 2010 at 02:38:26
Specs: Windows XP
I am trying to extract some data on dates to a dashboard to present it in a pie chart.

I have a list of dates relating to when something was tested. I need to establish which ones were tested in the last 3,6,12,12+ months, and those that haven't been tested (Blanks) then extract it onto another sheet on the document as simple numbers.

Can anyone help?


See More: IF statement. Dates with different outcomes

Report •


#1
April 7, 2010 at 04:24:27
Hi,

For this example the dates, including some blank cells, are in the range B2:B200,
Use this formula to return the number of dates no more than 30 days before today:

=SUMPRODUCT(($B$2:$B$200>=NOW()-30)*(1))

To count the number of dates in certain ranges, use this formula:
=SUMPRODUCT(($B$2:$B$200>=NOW()-60)*($B$2:$B$200<NOW()-30)*(1))

Repeat for each required range, and to pick up the remaining dates use this formula:
=SUMPRODUCT(($B$2:$B$200<NOW()-365)*($B$2:$B200<>"")*(1))
adjusted to match the end of the last range of dates. Note that this formula excludes counting empty cells.

Finally to count empty cells use this formula:

=SUMPRODUCT(($B$2:$B$200="")*(1))

If these formulas were on another sheet and the dates are on Sheet1, you get something like this:

=SUMPRODUCT((Sheet1!$B$2:$B$200>=NOW()-60)*(Sheet1!$B$2:$B$200<NOW()-30)*(1))

Regards


Report •

#2
April 7, 2010 at 05:17:53
Thank you very much,

That worked perfectly.

cheers!


Report •

#3
April 7, 2010 at 06:46:31
You're welcome

Regards

Humar


Report •
Related Solutions


Ask Question