I have a base where I have a list of invoices which have a date an an amount. I need to know the average earnings for each working day of the month the invoice is billed.
To do so, I wanted to calculate the number of working days for each month of the invoices. I saw that there’s the WORKINGDAY DIFF formula, but to make it works I’ll need the first and last day of each invoice, as the screenshot below:
Here, for example, I entered manually the 1st and last, but is there a way to automate this?
With my manually entered 1st and last date of the month, I calculated the working days for the month.
Then, I created a formula which calculate the average amount billed each day from the amount of the bill (Formula is: Amount of the bill divided by the # of working days in the month).
Finally, I sum all the bills of the month to know the average amount billed by day for the whole month. See my screenshot below:
I don’t really know if I’m heading in the right direction for that and would love some help. Is there a simpler way to do this? Is there a way to know automatically the 1st and last days of the month? In a second time, I’ll also need to sum it up, not for a month, but for a quarter, and I have no clue to work it out yet.