Find Missing Days when service was active only part of the year

[Dzip]

Hi,

I need help calculating a compliance % - I have 3 tables Sites, FSM, Days of year. Sites is a simple list of sites and the date the service was live from, Days of Year is just 365 calendar days. FSM are the reports submitted using MS forms. Site table is related to FSM through "Site Name" and Days of year is linked to FSM via the date of completion (on FSM Table).

I can produce a table like the below which works fine, the days in period adjusts with an excel timeline. The issue comes when the site has only been active part of the year e.g it went live in December. In this case it shows as non compliant when tested against a 365 days (having been active for only 31). I need a way to allow the days in period to account for the timeline in excel but also consider that a service may be active for only a proportion of that time. The Sites Table does contain a "Live Date" column. Any help really appreciated.

Site	No of Reports.	Days in Period	Compliance %
Site 1	365	365	100
Site 2	36	365	10%

 

[Toadstool]

Hi Dzip,

This seems to rely on the Days in Period calculation. You'll need to specify the period, then MAX of start of the period and Live Date. If it may be the current period (i.e. a YTD report) then you'll use the MIN of TODAY() or last day of period.

 

[Dzip]

Hi Dzip,

This seems to rely on the Days in Period calculation. You'll need to specify the period, then MAX of start of the period and Live Date. If it may be the current period (i.e. a YTD report) then you'll use the MIN of TODAY() or last day of period.

Thanks for your reply, how would I reference an excel timeline as the start of period and the live date within the MAX()? The idea is that the period would very based on the timeline.

Kind Regards

 

[Toadstool]

Maybe something like this:

DZip.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Site	No of Reports.	Days in Period	Compliance %			Report Period		Sites	Live Date
2	Site 1	365	366	99.73%			2020		Site 1	01-Jan-20
3	Site 2	36	31	116.13%					Site 2	01-Dec-20
4	Site 3	33	0	0.00%					Site 3	04-Apr-21
5	Site 4	33	0	0.00%					Site 77	01-Jan-19
6	Site 77	366	366	100.00%					Site 88	6/1/2020
7	Site 88	214	214	100.00%
Sheet1

Cell Formulas
Range	Formula
C2:C7	C2	=IFERROR(MAX(0,(MIN(TODAY(),DATE($G$2,12,31)))-(MAX(DATE($G$2,1,1),INDEX($J$2:$J$9999,MATCH(A2,$I$2:$I$9999,0))))+1),0)
D2:D7	D2	=IF(C2>0,B2/C2,0)