How to find the number of days worked in week where a week contains split month in excel

Question

I am trying to get the total number of days worked in a week, however also considering where a week has 2 separate months, the number of days should only display for the current month. i.e.

Week Ending
09/07/2023 - 5 Days
16/07/2023 - 4 days
23/07/2023 - 5 Days
30/07/2023 - 5 Days
06/08/2023 - 1 Day (since 31/07/2023 is a part of Aug week)

I tried to put the normal NETWORKDAYS(E50,F50,$H$49:$H$61) but it only uses the start date and end date but does not consider where some weeks can have split months in there.

Answer 1

It is not clear from the question the real scenario, and also for 7/16/2016 I would expect 5 days in the output, because all days of the week are part of the same month, i.e. I would say 4 is typo. I hope the idea works for your real case and if that is not the case, please update the question and let me know. Thanks.

You can calculate it manually as follow. The following formula spills the entire result all at once (formula 1):

=MAP(A2:A6-6, LAMBDA(s,SUM(N(MONTH(s+SEQUENCE(5,,0))=MONTH(s)))))

You can use NETWORKDAYS as follow for an array solution, maybe less intuitive. The idea is to build the holiday list properly excluding dates of the range if the month is not the same or defining the holidays for days out of the range (e+1 or e since it is a Sunday, i.e. non working day) when the month is the same:

=LET(end,A2:A6, MAP(end-6,end, LAMBDA(s,e,
 NETWORKDAYS(s,e, IF(MONTH(s+SEQUENCE(7,,0)) = MONTH(s),
  e + 1, s + SEQUENCE(7,,0))))))

Here is the output:

The condition A2:A6-6 finds the previous Monday for a given set of dates (A2:A6), assuming all end of the week days are Sundays.

Note: For general case, to get the previous Monday for a given day (not necessary a Sunday), you can use: A2:A6-WEEKDAY(A2:A6)-5.

For formula 1 we use MAP function to iterate over all start dates (s). It asumes working days are from Monday to Friday, so to generate each working day of the week it uses: s+SEQUENCE(5,,0). It uses MONTH function to identify the working days that belong to the same month as s. We use N() to convert the comparison to 0,1 values and finally use SUM function will count the working days of the same month as the given start date s.

Answer 2

Because all your data is always a sunday just count the weekdays between reference date minus 7 *and the minimum value between your reference date and end of month based on current date.

=WEEKDAYS(A1-7;MIN(A1;EOMONTH(TODAY();0)))

Anyways, at soon as date is August the output of last cell will be updated and will show a different result on weeks falling in 2 different months.