Calculate workdays for a month

Hi @ll.

I believe there are a lot of SeaTable users out there who are trying to build the best time table. Me too! :slight_smile: And one of my goals was to have a column that can return the workdays per month to compare the actual performed workdays.

This is a simple version, yeah! It does not include holidays. Well, maybe the next days I’ll have it included since I am tracking the holidays in an extra table. But this is another story.

How does it work?

The first two columns are simple numbers, year and month.

FirstDayWeekdayNumber: contains the calculated weekday of the first day of the given year + month
The following formulas are based on Monday as the first day of the week, as it is in Germany.

weekday(date({Year}, {Month}, 1), 'Monday')

TotalDaysThisMonth: Contains the total number of days of the given year + month

dateDif(
  eomonth(
    date({Year}, {Month}, 1),
    -1
  ), 
  eomonth(
    date({Year}, {Month}, 1),
    0
  ), 
  'D'
)

WorkdaysThisMonth: Calculates the workdays of the given year + month. This is the essential formula.
Depending on the TotalDaysThisMonth value it decides how many days it has to subtract from TotalDaysThisMonth.

1 = Monday, 2 = Tuesday, ..., 6 = Saturday, 7 = Sunday

If your first day is Sunday if will look like this:
1 = Sunday, 2 = Monday, ..., 6 = Friday, 7 = Saturday
But sind it is just counting the days that shouldn’t make any difference!

{TotalDaysThisMonth} -
ifs(
  and(
    {TotalDaysThisMonth} = 31,
    or(
      {FirstDayWeekdayNumber} = 5,
      {FirstDayWeekdayNumber} = 6
    )
  ), 10,
  and(
    {TotalDaysThisMonth} = 31,
	or(
	  {FirstDayWeekdayNumber} = 4,
      {FirstDayWeekdayNumber} = 7
	)
  ), 9,
  and(
    {TotalDaysThisMonth} = 31,
	and(
	  {FirstDayWeekdayNumber} >= 1,
      {FirstDayWeekdayNumber} <= 3
	)
  ), 8,
  "a" = "a", 0
) -
ifs(
  and(
    {TotalDaysThisMonth} = 30,
    or(
      {FirstDayWeekdayNumber} = 6,
      {FirstDayWeekdayNumber} = 7
    )
  ), 10,
  and(
    {TotalDaysThisMonth} = 30,
    {FirstDayWeekdayNumber} = 5
  ), 9,
  and(
    {TotalDaysThisMonth} = 30,
	and(
	  {FirstDayWeekdayNumber} >= 1,
      {FirstDayWeekdayNumber} <= 4
	)
  ), 8,
  "a" = "a", 0
) -
ifs(
  and(
    {TotalDaysThisMonth} = 29,
    or(
      {FirstDayWeekdayNumber} = 6,
      {FirstDayWeekdayNumber} = 7
    )
  ), 9,
  and(
    {TotalDaysThisMonth} = 29,
	and(
	  {FirstDayWeekdayNumber} >= 1,
      {FirstDayWeekdayNumber} <= 6
	)
  ), 8,
  "a" = "a", 0
) -
if(
  {TotalDaysThisMonth} = 28,
  8,
  0
)

Give it a try.

Cheers
AkDk7

1 Like

Very cool, thanks for sharing. An alternative could be to try the networkdays function, it may be able to help account for holidays too!

Thanks!

Yeah, I saw the function but was distracted by the holiday parameter.
networkdays(startDate, endDate, holiday1, [holiday2, ...])

Oh, yes, I see, it’s an optional parameter but that is not obvious in Seatable. It’s more clear in the formula reference.

You are right. The documentation lacks a bit of clearness… But it was still fun to code it :slight_smile:

Hm, seems I still have an error in the formula.