Counting number of weekend days per month in a date range

[flightoftheconcord]

I am running into an issue trying to count by employee and by month, the number of weekends in time away requests that span multiple months.

In one sheet "EmployeeList", I have a column with a list of all of my employees and have columns for each month of this year and next (Jan 2019 through Dec 2020) and in once we are through 2019 that will be updated to be 2020 through 2021, and all 2019 data would be removed. But I'm not worried so much about that yet.

In the other sheet "Requests" I have 3 columns: Employee Name, Request Start and Request End.

What I am wanting to track in the EmployeeList is when a Request is entered in the Requests sheet, it calculates the number of weekend days within that request and counts them under the corresponding month. But what gets tricky is employees can submit requests that may include weekends across two months.

Example would be a request for July 26 to August 5. That includes one weekend in July and one in August.

If I needed to just count weekends overall and didn't care about the specific month, I could figure that out pretty quickly but I can wrap my brain around how to make this work.

Any help would be appreciated!

 

[flightoftheconcord]

I am running into an issue trying to count by employee and by month, the number of weekends in time away requests that span multiple months.

In one sheet "EmployeeList", I have a column with a list of all of my employees and have columns for each month of this year and next (Jan 2019 through Dec 2020) and in once we are through 2019 that will be updated to be 2020 through 2021, and all 2019 data would be removed. But I'm not worried so much about that yet.

In the other sheet "Requests" I have 3 columns: Employee Name, Request Start and Request End.

What I am wanting to track in the EmployeeList is when a Request is entered in the Requests sheet, it calculates the number of weekend days within that request and counts them under the corresponding month. But what gets tricky is employees can submit requests that may include weekends across two months.

Example would be a request for July 26 to August 5. That includes one weekend in July and one in August.

If I needed to just count weekends overall and didn't care about the specific month, I could figure that out pretty quickly but I can wrap my brain around how to make this work.

Any help would be appreciated!

I should clarify that in Requests, Employee Name is Column A, Request Start is B, and Request end is C.
In EmployeeList, Employee name is column A and then then the months for 2019 are B through M, and 2020 is N through Y.

I have gone into requests and put all of the data from January 2019 through what has been requested by the employee so far, so from now on all of the data will be future data, but I would still like it to count what has already occurred this year if that makes sense.

 

[Tom.Jones]

Hi,

I should clarify that in Requests, Employee Name is Column A, Request Start is B, and Request end is C.

Say in B2 is start date in C2 end date you need in - say - D2 number of weekends? What if is half of weekends? Or you need number of days (Sat and Sun)?

 

[flightoftheconcord]

Hi,



Say in B2 is start date in C2 end date you need in - say - D2 number of weekends? What if is half of weekends? Or you need number of days (Sat and Sun)?

Yea, sorry I should clarify, I really only need the number of weekend days, not the specific dates. What I am trying to track is that we allow our team members to book off up to 4 weekend days a month, so if they do every Sunday that's okay, or if they do 2 Saturday and 2 Sundays that's fine, or 2 full weekends, etc. but we just want to track how many weekend days they have booked off each month.

As an example, if Johnny booked off January 19 as one request, and February 10 as one, and February 23 and 24 as one.

Requests would look like:

Name | Start | End

Johnny AppleSeed | January 19, 2019 | January 19, 2019
Johnny AppleSeed | February 10, 2019 | February 10, 2019
Johnny AppleSeed | February 23, 2019 | February 24, 2019

So in the EmployeeList sheet it should look like this:

Name | January | February
Johnny AppleSeed 1 3