Calculate number of Tuesdays between two dates


#1

I have two columns Start Date and End Date and a third column Working Days which defines the exact days of the week during which a person works (Monday, Tuesday, Wednesday, etc.).

I’d like a column to calculate the number of occurrences of any particular day or days within the date range defined by Start Date and End Date.

Example

Start Date End Date Working Days Total Work Days
4/15/2018 5/15/2018 Monday 5
4/15/2018 5/15/2018 Wednesday 4
4/15/2018 5/15/2018 Thursday 4
4/15/2018 5/15/2018 Monday,
Wednesday,
Thursday
13

#2

To start fill in the day of the weekday number that we want to count in a number field. The weekday number and not its name is important to do a calculation later. (Sun = 0, Mon = 1, Tues = 2, etc.)
field {Working_Day_Number}

We need to get number of days between start and end dates. (the output is in seconds, so /86400 to get days)
field {number_days}
DATETIME_DIFF({End},{Start})/86400

we will need to know the weekday number of the Start date
field {start_day_number}
DATETIME_FORMAT({start},'d')

Each weekday will occur every 7 days, so when we only look at the number of days leftover once we remove all the whole weeks from the total number of days, we want to know if the weekday of interest will occur in that remainder number of days

field {min_days}
ABS({start_day_number}-{Working_Day_Number}

field {remainder_day}
IF({min_days}<MOD({number_days},7),1,0)

Then some final logic

field {Total Work Days}
IF({start_Day_number}={working_day_number},ROUNDUP({number_days}/7,0),ROUNDDOWN(number_days/7,0)+remainder_days)

Seems to work as seen here:

This can definitely be consolidated to fewer cells, but for easier understanding it separated in multiple fields

Something additional will be needed in order to use the weekday name in place of the weekday number.

this may help with back formatting to a weekday name