Help

Calculate number of Tuesdays between two dates

3748 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Thomas_Gelder
4 - Data Explorer
4 - Data Explorer

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
1 Reply 1
Mike_McLaughlin
8 - Airtable Astronomer
8 - Airtable Astronomer

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:
Screen Shot 2019-02-07 at 12.32.03 AM.png

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...