Forum Discussion
How would I find missed services by month? (Spreadsheet Included)
Hi everyone, I'm stumped on an Excel formula here. I have a data set that shows each time we have serviced multiple properties. The expectation is that we service once per week on average (7 cale...
A calendar month does not correspond to a whole number of weeks. How do you define the number of missed services in a calendar month?
Hi Hans,
You are correct. My idea would be to count the days between the first and last dates serviced for the month, then calculate how many times that property should have been serviced vs how many times it was serviced.
For example, Let's say the first date for the property in April is 4/5, and the last date is 4/27 and we serviced the property twice in that month. Ideally the formula would calculate 22 total days serviced, then count the 2 total services vs the 3 times it should have been serviced, resulting in 1 missed service for April.
I can't think of any other way to properly define/calculate it for each month otherwise.
You are correct. My idea would be to count the days between the first and last dates serviced for the month, then calculate how many times that property should have been serviced vs how many times it was serviced.
For example, Let's say the first date for the property in April is 4/5, and the last date is 4/27 and we serviced the property twice in that month. Ideally the formula would calculate 22 total days serviced, then count the 2 total services vs the 3 times it should have been serviced, resulting in 1 missed service for April.
I can't think of any other way to properly define/calculate it for each month otherwise.
But you could also do it the following way: April has 30 days, so you'd expect 30/7 ~ 4.3 services. Missing: 4.3-2 ~ 2.3, or 2 if you will.
- I could, but I believe I left out some info at the beginning. We don't always start the services at the beginning of the month and end at the end of the month. Sometimes we aren't requested to start until, let's say 4/15. That's essentially what the "Date Serviced" field helps to capture.
But what if you missed the first service of a month, i.e. you were supposed to start on April 8, but you actually started on April 15, or even on April 22?
