Suggestions and help respectfully requested.
I found a calendar for excel that I have attached. I'd rather use this than the Occupancy Rates 202-2023 attached workbook. I just do not know how to make the Monday Morning Report Calculations integrate with Occupancy Rates Calendar.
Help Please!!!!
I have a worksheet that consists of each month with columns under each month that contains the day number of each Monday and under each Monday Day Number there is a cell for a percentage number that is created from data entered into cells retrieved from another workbook. What I am wondering is if there is a calendar function or some way to integrate this together some how? I have a report that is run each Monday of every month that shows data based on Occupancy. This report shows properties that are unrented and a status of why. This report groups these properties based on the reason to why they are not rented. Such as: Total Vacant Unrented Not Ready: 14, Total Vacant Rented Ready: 2, Total Vacant Rented Not Ready: 8, Total Down: 8, Total Vacant Ready: 0, Total Notice Rented: 5 and Total Notice Unrented: 11. The counts at the end of the Reason Status will usually be different or can be different each Monday or be the same. I have a worksheet that automatically pulls numbers from the report based on formulas I've created and places the the numbers accordingly. The worksheet knows what report to use based on the name of the report entered into a cell on the sheet. The name on the sheet is made up of words along with a date. IE... UnitAvailabilityDetails02_07_2023.xlsx. This worksheet based on the numbers pulled into it creates Occupancy Rate Percentages for 2 categories. These Rates can be found in cells B9 & E16. I have to capture these Rates/Percentages on another worksheet that will be a weekly collection of the percentages from which I can get a monthly average of occupancy percentages.
What I am trying to do is capture the Day of Month from the workbook name entered. So in the case of the name shown above, I would like to capture the "7" and have it entered into the cell for the day of the month and the percentages from B9 & E16. Even though we usually run this report every Monday, there are some cases it may not be on a Monday and therefore the day of the Month is important. This number is to be recorded in a column under the month for which it applies. IE: JAN-23. I have attached a Workbook "Occupancy Rates 2022-2023.xlsx" for where the day of the month and the Percentages are to be recorded. I am hoping there is a way to move this data automatically based on the use of formulas.
To recap, the Monday Morning Report Calculations are calculated by producing the UnitAvailabilityDetails02_07_2023.xlsx report. The day of the Report along with the Percentages calculated get recorded into the Occupancy Rates 2022-2023 workbook. I'm trying to automate moving the data into the Occupancy Rates workbook.
If there is a better way to record the percentages into a calendar workbook based on the day of the report I am way open to it.
