Microsoft Excel - create patient revenue by month using multiple criteria for two years

Hi, I need to calculate revenue by patient by month based on multiple criteria. For example, I have this information on: Patient Name, daily current gross rate, current discount rate, new gross rate, new discount rate, admission date, discharge date, type of rate: per day or per month. With this information I need to calculate the revenue by month using: daily rate * number of days in a month or flat rate per month. Number of days can be total days of the month or less if the admission and or discharge date are in the middle or end date. For example, 09/15/2022 = 15 days.

I need a VBA code that calculate the patient revenue by month based on all these criteria and populate the numbers. If a patient has new rate and or new discount rate, then we should use the new rates. I need to forecast and budget for the patient revenue, which means, I need 12 months for 2022 and 12 months for 2023. The VBA code should allow me to select the years.

I also need the number of patients by month. If a patient was only 5 days in a month, then the average number should be 5/30 if we are in a month with 30 days or 5/31 or 5/28, and so forth. I have attached a sample file.

Thanks for your help.

I am trying to attach a sample file. It is not allowing me. How can I do that?