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 calendar days). I'm trying to find out how many services have we essentially missed if we did not service every 7 days.
For example, see Property ID: 11111111. Our first service was on 4/24 and our last service was 6/14. In total, we've serviced this property 4 times over the course of 51 days. Following the "every 7 day" rule, we should have serviced over 7 times (51/7) for a total of 3 missed services.
I would need the missed services broken down by month, if possible, and for each client (column D).
9 Replies
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.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.
