Calculate number of Tuesdays between two dates

Thomas_Gelder · ‎Feb 06, 2019

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

Mike_McLaughlin · ‎Feb 06, 2019

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

Supported format specifiers for DATETIME_FORMAT

The DATETIME_FORMAT function will allow you to reformat the data from the date-type field into a string of your specifications. This is written in the form "DATETIME_FORMAT(Datetime, 'format s...