Daily Average Number of Cases Open- Community Services
Dear Members/super contributors, In community services we have to report on daily average number of clients in the care program per month. This is calculated under following conditions: 1. If the client was in care program prior to start of the month and stayed in the care till the end- then we count as number of days in the month. 2. if the client joined the care program within the month then we take datediff between the date of joining and the end of the month date. 3. if the Client left the program within the month under consideration then we do the datediff between the start of the that month and the day they left the program. We add all these numbers and divide them by the number of days in the month to calculate daily average. If someone can help me with syntax of multiple if/ any other function and multiple datediff's or any other way to approach it. My data looks like this and the last column is the intended solution Thanks in advance
