HELP!! Need a formula to count days if the days are in a specific week number

Melissa Boehl

Hello everyone! I hope someone can help me figure this out! I am quite good with formulas but for some reason, this has me stumped completely!

I have developed a Time Off Request process for our company. There is a start date and end date. I have columns that return week number based on the date range. Basically I have a column that returns the week number for start date and a column for week number for end date. Then an additional column that returns the number of weeks within the range. In order for me to calculate the average number of PTO hours used per week, I need to count the number of days the user will be out in each week number.

I know this seems really complex for no reason but there is a purpose for the madness. Teams want to be able to look at a chart and see the average number of hours people are out for the next few months to plan workload. For example, if a team is already showing that there team is using 200 hours of PTO next week, they may choose to decline any further requests for that week.

I have been just spreading the time evenly between the number of weeks. The problem with this is, if someone submits a request that starts on Thursday of Week 1 and ends on Thursday of Week 3, diving the hours evenly doesn't truly reflect how much the person would be out. If I divided the number of PTO hours (88 hours) by 3 weeks, I would get approx. 29 hours per week. When in reality, only 16 hours is used in week 1, 40 in week 2, and 32 in week 3. When calculating this way for teams of 40 or 50 people, this can cause significant fluctuations in the chart.

If I can return the number of days per week, I could divide the hours by the number of days for each week. i.e. Week One (2 days): (88 hours / 11 days) * 2 Days =16; Week Two (5 days): (88 hours/11 days)*5 days = 40 hours; and Week 3 (4 days): (88 hours/11 days)*4 days = 32 hours.

I am including some screenshots of a basic example of what I am trying to do. Thanks for your help!

Dan W

I think this may help?

With some more helper columns you could use =Date@row + (6 - WEEKDAY(Date@row)) which will give you the Friday of the week based on any date. Then you can use those dates to find the days per week.

Dan W

I think this may help?

With some more helper columns you could use =Date@row + (6 - WEEKDAY(Date@row)) which will give you the Friday of the week based on any date. Then you can use those dates to find the days per week.

Melissa Boehl

@Dan W Thanks! I really appreciate your time. I was really just drawing a blank there.... LOL