Hi,
I have 2 dog shelters (3 units in 2 locations) and my end goal is to have a line graph with the month on the X axis showing the % of how many days per month the units are unused.
- the Start_Date_Unoccupied is when a dog gets adopted and the unit becomes unused
- the End_Date_Unoccupied is when a new dog fills that unit
- the Days_Between count is the number of days a unit is empty
- Total_Monthly_Possible_Count - average of days per month to use for calculation
| Region | City | Dog | Total_Units_In_Shelter | Start_Date_Unoccupied | End_Date_Unoccupied | Days_Between | Total_Monthly_ Possible_Count |
| North | Glasgow | Max | Bedspace1 | 04/04/2024 | 10/04/2024 | 6 | 30.43 |
| North | Glasgow | Rex | Bedspace2 | 06/04/2024 | 18/04/2024 | 12 | 30.43 |
| North | Glasgow | Milly | Bedspace3 | 07/04/2024 | | | 30.43 |
| South | London | Buddy | Bedspace4 | 27/04/2024 | 14/06/2024 | 48 | 30.43 |
| South | London | Blue | Bedspace5 | 22/05/2024 | 11/06/2024 | 20 | 30.43 |
| South | London | Alba | Bedspace6 | 06/06/2024 | | | 30.43 |
In Milly's example, the End_Date is blank and will remain blank until she is adopted. How can I make this cell appear as 30/04/2024 & 30 days between for example?
In Buddy's example, the unit is unoccupied from April to June. As it started on the 27th of April, how can the total for April only be 3 days? (and the same principle for May and June onwards)
How I need the graph structured. The % on the line graph should show:
April 28% (51 days unused against the 182 possible unused days for the 6 shelters combined)
May 36% (66 days unused against the 182 possible unused days for the 6 shelters combined)
Mays breakdown:
Unit 4 has 31 days
Unit 5 has 10 days
Unit 6 has 25 days
Thanks so much for your help in advance! Please let me know if you have any questions.
