To enter formula to display date range if dates is greater than 30, 60 and 90 days

[shizi_uk]

Hi All,

Please could you assist in the following;

1. On a monthly basis I am required to pull a report from SharePoint displaying documents that are under review and overdue by 30 days, 60 days and 90 days from the date range. (1, 2 and 3 months).

2. And from the total I am required to show it as a percentage on a monthly basis for e.g. 5 sites in the monthly of Jan had 20% of their docs that were overdue by 30 days, 10% 60days etc.

I've tried a few formulas but the results seem to vary and not accurate.

Could anyone assist in the two queries above.

Thanks

 

[maabadi]

Try this:

Book1
	G	H	I	J	K	L	M
1	Under review	Date
2	TRUE	7/16/2020		Summary Total
3	TRUE	7/24/2020		30	60	90
4	TRUE	8/1/2020		22	18	14
5	TRUE	8/9/2020
6	TRUE	8/17/2020
7	TRUE	8/25/2020
8	TRUE	9/2/2020
9	TRUE	9/10/2020
10	TRUE	9/18/2020
11	TRUE	9/26/2020
12	TRUE	10/4/2020
13	TRUE	10/12/2020
14	TRUE	10/20/2020
15	TRUE	10/28/2020
16	TRUE	11/5/2020
17	TRUE	11/13/2020
18	TRUE	11/21/2020
19	TRUE	11/29/2020
20	TRUE	12/7/2020
21	TRUE	12/15/2020
22	TRUE	12/23/2020
23	TRUE	12/31/2020
24	TRUE	1/8/2021
25	TRUE	1/16/2021
Sheet1

Cell Formulas
Range	Formula
J4:L4	J4	=SUMPRODUCT(($G$2:$G$25=TRUE)*((TODAY()-$H$2:$H$25)>J3))

 

[shizi_uk]

Hi Maabadi,

Thanks for replying.

I get the following result:

Maybe to get a more accurate result it should be the date which is <=30 and so on?

The dates in H column are the dates when the document has expired for review and counting manually the formula should have shown 3 days for 30 days.

Your thoughts?

 

[maabadi]

For <=30 Days Use:

=SUMPRODUCT(($G$2:$G$25=TRUE)*((TODAY()-$H$2:$H$25)<=J3))

And for others, it depends to yourself that what you want

 

[shizi_uk]

Thanks Maabadi,

I'll try this.

 

[maabadi]

You're Welcome.