I am in a fix and I know there is a solution somewhere for this. I have a data set in which I need to multiply the total capacity to the monthly days number and secondly i need to multiply the total capacity of the unit to DOW e.g. 4 sundays, 5 mondays , 4 wednesday of one month or more. I need this to be dynamic.
My current formula goes like this.
---- [Hotel Occupied Rooms] / (30*[No of rooms])
30 = is the number of days in that month it can be 31 the next month and 30 the month later
But what now if I want to apply the same formula for '' days of week'' I possibly cannot break down every single day and count how many times it appears on the calendar and even if i do how do I build a formula which can be dynamic without me doing the manual work.