Number of visits per month

Hi Experts

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.
Number-of-visits-per-month.xlsx
LVL 4
JorgenConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
Could you please define how to determine whether the first of the month is in an even week or an odd week? I realize that you are based in Europe and may be using the ISO system--but your client may be using the American system. Basically, how would you use the WEEKNUM function with that date?
0
byundtMechanical EngineerCommented:
I reproduce your results with this formula in cell P2:
=SUMPRODUCT((WEEKDAY($B$11+ROW($1:$31)-1,2)={1,2,3,4,5})*(ISEVEN(WEEKNUM($B$11+ROW($1:$31)-1,2))*(B2:F2="X")+ISODD(WEEKNUM($B$11+ROW($1:$31)-1,2))*(G2:K2="X"))*(ROW($1:$31)<=DAY(EOMONTH($B$11,0))))
Number-of-visits-per-monthQ28736116.xlsx
0
JorgenConsultantAuthor Commented:
Hi Brad,

You are correct.  it is Danish dates and the system is counting days according to Danish rules - which is European.

It seems to Work great.
I have tested if it Counts correctly both for September and October

To understand what you did, I tried to debug your formula

I can see, that you insert the weekdays number in the first part of the formula, and compares that with the Workdays of the week to get TRUE and FALSE values

In the second part is it correct, that you create a list of TRUE and FALSE values based on if the week is even or odd combined with if there is a visit?

In the third part I can understand that you are actually measuring if the day should be included in the calculation, to secure that you only get 30 days for september, but will get 31 for october.

To be honest I think I could have spend weeks on this formula, so it could be great to understand in depth what you are doing.

That is if you have the time to explain a Little more

regards

Jørgen
0
byundtMechanical EngineerCommented:
Jørgen,
ROW($1:$31)           returns a vertical array of the numbers 1 through 31 (representing the days of the month). Assumes you never insert or delete any rows between 1 and 31. If you cannot keep this promise, then use ROW(INDIRECT("1:31")) instead.
WEEKDAY($B$11+ROW($1:$31)-1,2)         returns a vertical array of the day of the week (Monday =1, Tuesday = 2, etc.) for each day of the month
(WEEKDAY($B$11+ROW($1:$31)-1,2)={1,2,3,4,5}       returns a rectangular array of the counts of Mondays, Tuesdays, etc. in the month
WEEKNUM($B$11+ROW($1:$31)-1,2))       returns vertical array of the number of the week for each day in the month, counting since January 1 equals week 1. If you want to count since the first Thursday of the year equals week 1 (ISO 8601standard), then use WEEKNUM($B$11+ROW($1:$31)-1,21))
ISEVEN(WEEKNUM($B$11+ROW($1:$31)-1,2))       returns vertical array of TRUE/FALSE depending on whether week number for each day of month is an even number
(B2:F2="X")          returns a horizontal array of TRUE/FALSE depending on whether B2:F2 equals X
ISEVEN(WEEKNUM($B$11+ROW($1:$31)-1,2))*(B2:F2="X")    returns a rectangular array of 1 and 0 if week number (of each day of month) is even and column for Monday through Friday is marked X
ISODD(WEEKNUM($B$11+ROW($1:$31)-1,2))*(G2:K2="X")    similar test returning rectangular array of 1 and 0 if week number (of each day of month) is odd and column for Monday through Friday is marked X
EOMONTH($B$11,0)       returns date of last day of month
DAY(EOMONTH($B$11,0))       returns number of days in the month
(ROW($1:$31)<=DAY(EOMONTH($B$11,0)))     returns vertical array of TRUE/FALSE depending on whether the numbers 1 through 31 are valid days within the month

Putting it all together, the SUMPRODUCT adds the ISEVEN and ISODD values to get a rectangular array of 1 and 0, then multiplies it by the TRUE/FALSE results from the other array expressions. TRUE is converted to 1 and FALSE to 0 in so doing. Multiplying a vertical array times a horizontal array returns a rectangular array. Multiplying a rectangular array times either a vertical or horizontal array returns a rectangular array. So the net result is a rectangular array that satisfies all combination of tests, which SUMPRODUCT then adds up.

For what is is worth, I build formulas like this a piece at a time, testing as I go. My first step was to count the number of Mondays in a month. My second step was to figure out how many occurred in an even week. My third step was to count the number of even week Mondays & Tuesdays and odd week Wednesdays & Thursdays (for example) had an X.

Brad
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JorgenConsultantAuthor Commented:
Thanks Brad

I did not expect that detailed an explanation, but I am fully aware that the more complex array formulas is a part of Excel, where I can improve. And your explanation will be a big help on that.

regards

Jørgen
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.