Sum total days in date range which fall on certain days of the week

CAH

Where the first instance of 'Days Not Worked' for a member of staff occurs (the first date of that person's annual leave), I want to input a distinct value (only once) in the column 'Deduct N/A Annual Leave'.

This is the sum of, where manager type is Company annual leave, the number of days in a date ranges between Start/Finish that that fall on any of the selected days in the multiple value dropdown column 'Days Not Worked'.

Please see screenshot of relevant columns and link to another post that was similar to what I wanted, but I'm not sure how to convert to my need.

Any help gratefully received, many thanks in advance.

https://community.smartsheet.com/discussion/110815/if-date-range-includes-a-friday-check-a-box

jmyzk_cloudsmart_jp

Hi @CAH

I've interpreted the problem as follows:

https://app.smartsheet.com/b/publish?EQBCT=a6ed837b45be44a6b339ec9105cbdf67

In the '[Days Not Worked]' column, you'll find either a 4 or a 6, which corresponds to Thursdays and Saturdays. However, these days are considered company weekly holiday so taking a vacation on these days doesn't count as annual leave. To put it simply, 'Days Not Worked' can be a bit confusing, but I see it as 'days off on those days that don't eat into your annual leave' (N/A Weekdays).

In essence, if a store is closed on Thursdays and Saturdays, taking those days off isn't considered a vacation day.

This scenario poses a general challenge when calculating the number of vacation days taken when a company or store receives a vacation request. Even if the vacation starts and ends on specific dates, the days when the company or store is closed shouldn't be counted as vacation days.

Calculating this using only the table is straightforward when the vacation spans a single week. However, when it stretches over multiple weeks, it becomes more complex. For instance, counting the 4s and 6s between the 'WeekDay' corresponding to the start and end dates is easy when it's within the same week. But for a three-week vacation, the calculation becomes intricate. In the first week, you count how many 4s or 6s fall between the 'WeekDAY' corresponding to the start and the seventh day. In the middle week, you simply count two. And in the last week, you count how many 4s or 6s fall between the first day and the 'WeekDAY' corresponding to the end date.

To simplify this process, you can create a separate table, similar to a calendar, with dates and their corresponding weekdays for an entire year. Then, you can specify the range using the start and end dates and count the corresponding weekdays using COUNTIFS. This eliminates the need for complicated case divisions.

In the formula below, '{WeekDay}' and '[NA2]@row' represent the 'WEEKDAY' and 'DATE' columns from another table. '[NA1]@row' corresponds to 4, and '[NA2]@row' corresponds to 6.

• =IF([Manager Type]@row = "Company Annual Leave", COUNTIFS({WeekDay}, OR(@cell = [NA1]@row, @cell = [NA2]@row), {Date}, AND(@cell >= Start@row, @cell <= End@row)))