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?

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

Thank you very much, That worked perfectly.

cheers!

You're welcome Regards

Humar

Ask Your Question

Weekly Poll

Would you use "Spotlight Search" on Windows?

Discuss in The Lounge

Poll History