I have an extract from a transportation system, that can only extract the number of visits for a 2 week period.
Visits will be in a structured form, but they can be several times a week, once a week or biweekly.
The visits are listed in a 2 week overview exactly as the example shows
How can I count the number of visits for any given month based on the first day of the month
At the moment this is done by counting the number of visits in a 2 week period convert that to a 4 week period, and then every month evaluate the remaining days to see if They are workdays in either an event or odd week, and convert that to a possible visit (That is listed in the column Extra Days)
It takes a lot of time in our finance department to prepare that every month, and I hope to find a solution that can evaluate this 2 week period against a calender and decide how many visits each office has in the specific month
I have tried sumproduct to find how many times a specific weekday occurs in a month, but I need Excel to calculate how many times it occurs in odd or even weeks
I guess then I could put that on top of my biweekly list, and then calculate the number of visits with the number of occurences on for example Tuesdays in even weeks.