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
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
And a more simple solution : WORKDAY_DIFF({Starting Date},{Ending Date},' holidays]')