calculate the duration of a task from start to finish, excluding weekends.

Baserow Help
1

Are you using our SaaS platform (Baserow.io) or self-hosting Baserow?

Self-hosted

What do you need help with?

I want to calculate the duration of a task from start to finish, excluding weekends. However, baserow doesn’t seem to have either the WORKDAY() or WEEKDAY() function. How should I write the formula for the formula field?

2

@frederikdc do you happen to know any workarounds for this?

3

No, we currently have no way to detect weekend or weekdays.

Something that might work is the following formula:

field('Duration in days') - ((round(field('Duration in days') / 7,0) * 2) + 2) +  (tonumber(datetime_format(field('End'),'d')) - 1)

field('Duration in days')

is the result of your calculation of the number of days between the start and end date including weekends

((round(field('Duration in days') / 7,0) * 2) + 2)

I divide the number of days by 7 (days in a week) and multiple by 2 (two days in the weekend). This offers me the number of weekend days I need to subtract for the “full weeks” of the duration. I add 2 to this to include the weekend days of the first not-full week.

(tonumber(datetime_format(field('End'),'d')) - 1)

This formula returns the number of the day for the end date (2 = Monday, 7 = Saturday). I add this to the formula to compensate the the days of the last week that is not a full week.

I tested with a couple of values and the formula seems to work

4

On a second thought, this should also work and is much simpler

date_diff('day', field('Start'), field('End')) - (date_diff('week', field('Start'), field('End')) * 2)

You subtract the difference expressed in days from the difference expressed in weeks multiplied by 2 (saturday + sunday)