Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

# Calculating Difference between two dates, excluding weekends

Topic Labels: Formulas
473 1
cancel
Showing results for
Did you mean:
4 - Data Explorer

Hello,

I am trying to calculate the difference between two dates, excluding weekends. Datediff. However, weekend is Friday and Saturday at my company.
Workday doesn’t help much since it assumes weekends are Saturdays and Sundays.

Your help is highly appreciated.

9 - Sun

Hi there! It’s actually not so trivial with formulas only. Here’s how I have done it:

First, we need the days until next Friday, next Saturday and total amount of days between two dates (our two date fields are called ‘Start’ and 'End; calculations done in three separate fields).

Time until next Friday ({TnF}): `IF(WEEKDAY(Start)>5, 12-WEEKDAY(Start), 5-WEEKDAY(Start))`
Time until next Saturday ({TnS}): `IF(WEEKDAY(Start)>6, 13-WEEKDAY(Start), 6-WEEKDAY(Start))`
Total amount of days ({StE}): `DATETIME_DIFF(End, Start, 'days')`

Now we can calculate how many Fridays and Saturdays there are between the two dates (done in separate fields).

{How many Fridays?}: `IF(StE<TnF, 0, IF(StE=TnF, 1, ROUNDDOWN(((StE-TnF)/7),0)+1))`
{How many Saturdays?}: `IF(StE<TnS, 0, IF(StE=TnS, 1, ROUNDDOWN(((StE-TnS)/7),0)+1))`

And now we can subtract Fridays and Saturdays from the total date difference in days:

``````StE-{How many Fridays?}-{How many Saturdays}
``````

Maybe someone comes up with an easier way, but this works :slightly_smiling_face: