Python + SeaTable | Calculate the number of working days between two days

When we calculate the number of days between two days, we usually subtract the start date from the end date, but in actual project management, task management, work planning and other scenarios, some time periods will involve weekends, statutory holidays, and even There are also company-defined working time arrangements, so it is necessary to calculate the actual number of working days between the two days. For example, there are multiple tasks in a table, and each task has its own start date, planned end date or actual end date, so how to automatically calculate the actual number of working days between two days so as to be precise and quantitative?

At this point, the almighty Python can be used, and implementing it with Python + SeaTable will be more convenient for work management. This article focuses on sharing ideas and codes for reference only.

SeaTable tables have rich data types, such as dates, radios, collaborators, formulas, buttons, etc., which can manage all kinds of information conveniently and standardly. In addition, there are many basic functions and extended functions, including scripting functions. Click the "Script" button on the form, you can import or create multiple scripts, and you can run them at any time with one click (if you need to set regular running, etc., you can implement it in "Automation Rules").

For example, in the form below, the start time is filled in by the project manager; the end time is filled in by each task leader when completing the project; the working days (days) are calculated by running a Python script based on the start time and end time.

Create a new Python script on the SeaTable table

Specifically. First, we open the script function, select "New Script", and select Python.

ideas

In the opened interface, the script can be written.

There are several issues to pay attention to in the scripting process for calculating the working day here, taking China as an example:

• Working days and rest days in the coming year are not supported for the time being (because the country has not announced the arrangements yet).
• You need to define the days off during weekdays, that is, which days off from Monday to Friday.
• It is necessary to define the days of work on weekends, that is, which days on Saturday and Sunday will be adjusted off.

code

List the above special dates one by one, and this script is not difficult to write. Some script fragments are given below, taking 2022 as an example.

 import datetime
from enum import Enum
from seatable_api import dateutils, Base, context

# 一个 Base 的授权信息
SERVER_URL = context.server_url or 'https://cloud.seatable.cn'
API_TOKEN = context.api_token or 'dd46f9ca0172a850a0922107a6b2e6b99932b040'

# 1. 定义中国的节假日概况
class Holiday(Enum):
    new_years_day = "元旦"
    spring_festival = "春节"
    tomb_sweeping_day = "清明"
    labour_day ="劳动节"
    dragon_boat_festival = "端午"
    national_day = "国庆节"
    mid_autumn_festival = "中秋"

# 2. 列出节假日列表， 此处可以去查询日历，就不一一列出了
holidays = {
    datetime.date(year=2022, month=1, day=1): Holiday.new_years_day.value,
    datetime.date(year=2022, month=1, day=2): Holiday.new_years_day.value,
    datetime.date(year=2022, month=1, day=3): Holiday.new_years_day.value,
    datetime.date(year=2022, month=1, day=31): Holiday.spring_festival.value,
    datetime.date(year=2022, month=2, day=1): Holiday.spring_festival.value,
    .....
}

# 3.列出调休日的列表，及周六日为工作日的列表
workdays = {
    datetime.date(year=2022, month=1, day=29): Holiday.spring_festival.value,
    datetime.date(year=2022, month=1, day=30): Holiday.spring_festival.value,
    datetime.date(year=2022, month=4, day=2): Holiday.tomb_sweeping_day.value,
    datetime.date(year=2022, month=4, day=24): Holiday.labour_day.value,
    datetime.date(year=2022, month=5, day=7): Holiday.labour_day.value,
    datetime.date(year=2022, month=10, day=8): Holiday.national_day.value,
    datetime.date(year=2022, month=10, day=9): Holiday.national_day.value,
    ....
}

# 4. 定义是否是工作日
def is_workday(date):
    '''
    工作日定义：
    1. 日期在workdays字典的key中
    2. 星期是周一到周五且不在holidays字典的key中
    '''
    date = _validate_date(date)
    weekday = date.weekday()
    return bool(date in workdays.keys() or (weekday <= 4 and date not in holidays.keys()))
    
# 5. 计算两个日期之间的工作日， 此处返回的是工作日的列表， 该列表的长度即是工作日的天数
def get_workdays(start, end):
    """
    获取两个日期之间的工作日，返回datetime的列表
    """
    start, end = _validate_date(start, end)
    return list(filter(is_workday, get_dates(start, end)))
    
# 6. 将结果写入 SeaTable

def calculate_base_workdays(base, table_name):
    '''
    通过seatable表格中的，开始日期, 结束日期， 计算两个日期间工作日的天数，并把其更新到该行的
    工作日字段中
    '''

    for row in base.list_rows(table_name):
        row_id = row.get('_id')
        start_date = row.get("开始日期")
        end_date = row.get("结束日期")
        if not (start_date and end_date):
            continue
        try:
            work_day_list = get_workdays(start_date, end_date)
            # 两个日期间的工作日天数
            work_day_counts = len(work_day_list)
            cell_value = row.get("工作日")
            if cell_value == work_day_counts:
                continue
            base.update_row(
                table_name,
                row_id,
                {
                    "工作日": work_day_counts
                }
            )
        except Exception as e:
            print("start date: %s, end date: %s, error: %s" % (start_date, end_date, e) )
            continue
            
base = Base(API_TOKEN, SERVER_URL)
base.auth()
calculate_base_workdays(base, "工作任务安排")

The complete script can be referred to (link): Python script to calculate the number of working days

Summarize

As a new digital platform based on online collaborative tables, SeaTable is rich in functions and flexible in use, which can help us achieve integrated data management and processing. When we need to quickly develop a custom data processing process, we can use its complete Python API function, which can save a lot of costs. Specifically in this case, in addition to using Python to calculate the working days between two days, you can also use the calendar plug-in, timeline plug-in, and advanced statistics plug-in of the table to view and do visual chart analysis, making project management more convenient. Implementing applications is simpler.

---

References

Python + SeaTable | Get company financing and other information from Qixinbao website to SeaTable table

Python + SeaTable | Use Python to grab Winter Olympics city information from Wikipedia and make a map

Synchronize server logs to SeaTable for better visualization and collaborative processing of logs