Python薪资计算程序报错：datetime.time不支持减法运算的解决方法

问题概述 #

我用Python编写了薪资计算程序处理Excel考勤数据，其中一份数据可正常运行，但另一份数据运行时抛出错误：unsupported operand type(s) for -: 'datetime.time' and 'datetime.time'。

错误原因 #

1. Python原生不支持datetime.time类型直接做减法运算：程序中end_time - overtime_threshold这类代码直接操作time对象，会触发类型错误。
2. 未初始化overtime_hours变量：循环中使用overtime_hours +=但未提前定义，首次运行会抛出未定义错误。
3. 未处理跨天考勤场景：若考勤记录存在跨天（比如下班时间是次日凌晨），end_time会早于start_time，直接计算时长会得到负数，逻辑错误。
4. 冗余的薪资判断逻辑：大量if-elif判断员工薪资，代码冗余且不易维护。

修复方案 #

• 用datetime.datetime组合日期和时间，避免直接操作time类型
• 每次循环前初始化overtime_hours为0
• 增加跨天判断，若end_time < start_time，则时长计算加上24小时
• 用字典存储员工基础薪资和时薪，简化薪资计算逻辑

完整修正代码 #

# Install required library
!pip install xlrd openpyxl
import pandas as pd
from datetime import datetime, time, timedelta
import math

# Mount google drive
from google.colab import drive
drive.mount('/content/drive')

# Read the Excel file
path = '/content/drive/MyDrive/Colab Notebooks/Book1.xlsx'
df = pd.read_excel(path)

# Convert the 'Tgl/Waktu' column to datetime format
df['Tgl/Waktu'] = pd.to_datetime(df['Tgl/Waktu'])

# Extract the date and time from the 'Tgl/Waktu' column
df['Date'] = df['Tgl/Waktu'].dt.date
df['Time'] = df['Tgl/Waktu'].dt.time

# Group the data by employee name and date
grouped_df = df.groupby(['Nama', 'Date'])

# Set the overtime threshold to 16:30:00
overtime_threshold = time(hour=16, minute=30)

# Set the late limit
late_limit = time(hour=8, minute=15)

# Set holidays date
holidays_date = ['2023-1-1', '2023-1-22', '2023-2-18', '2023-3-22', '2023-4-7', '2023-4-22', '2023-4-23', '2023-5-1', '2023-5-18', '2023-6-1', '2023-6-4','2023-6-29',
                '2023-7-19', '2023-8-17', '2023-9-28', '2023-12-25', '2023-1-23', '2023-3-23', '2023-4-21', '2023-4-24', '2023-4-25', '2023-4-26', '2023-6-2', '2023-12-26',
                '2023-1-8', '2023-1-15', '2023-1-29', '2023-2-5', '2023-2-12', '2023-2-19', '2023-2-26', '2023-3-5', '2023-3-12', '2023-3-19', '2023-3-26', '2023-4-2',
                '2023-4-9', '2023-4-16', '2023-4-23', '2023-4-30', '2023-5-7', '2023-5-14', '2023-5-21', '2023-5-28', '2023-6-11', '2023-6-18', '2023-6-25', 
                '2023-7-2', '2023-7-9', '2023-7-16', '2023-7-23', '2023-7-30', '2023-8-6', '2023-8-13', '2023-8-20', '2023-8-27', '2023-9-3', '2023-9-10', '2023-9-17', 
                '2023-9-24', '2023-10-1', '2023-10-8', '2023-10-15', '2023-10-22', '2023-10-29', '2023-11-5', '2023-11-12', '2023-11-19', '2023-11-26', '2023-12-3', '2023-12-10', 
                '2023-12-17', '2023-12-24', '2023-12-31','2022-12-20']

# 定义员工薪资字典，简化逻辑
employee_salary = {
    'Alif': {'daily': 60000, 'overtime_rate': 10000},
    'budi': {'daily': 70000, 'overtime_rate': 10000},
    'adi': {'daily': 60000, 'overtime_rate': 10000},
    'supriyanto': {'daily': 70000, 'overtime_rate': 10000},
    'Edi': {'daily': 60000, 'overtime_rate': 10000},
    'Tri Gunawan': {'daily': 60000, 'overtime_rate': 10000},
    'Bayu Aji N': {'daily': 60000, 'overtime_rate': 10000},
    'dani': {'daily': 70000, 'overtime_rate': 10000}
}

# Iterate over the grouped data
for (name, date), group in grouped_df:
    # 初始化加班时长
    overtime_hours = 0.0
    
    # 获取当日最早和最晚的考勤时间，组合成datetime对象
    start_datetime = datetime.combine(date, group['Time'].min())
    end_datetime = datetime.combine(date, group['Time'].max())
    
    # 处理跨天情况：如果结束时间早于开始时间，说明跨天，加24小时
    if end_datetime < start_datetime:
        end_datetime += timedelta(days=1)
    
    # 计算总工作时长
    total_hours = (end_datetime - start_datetime).total_seconds() / 3600
    
    # 计算有效工作时长和加班时长
    if total_hours > 8:
        hours_worked = 8
        # 计算加班时长：结束时间超过16:30的部分
        overtime_cutoff = datetime.combine(date, overtime_threshold)
        if end_datetime > overtime_cutoff:
            overtime_hours = (end_datetime - overtime_cutoff).total_seconds() / 3600
    elif total_hours < 8:
        if group['Time'].min() > late_limit:
            hours_worked = 5
        else:
            hours_worked = math.floor(total_hours)
            overtime_hours = 0
    else:
        hours_worked = 8
        # 检查是否有加班
        overtime_cutoff = datetime.combine(date, overtime_threshold)
        if end_datetime > overtime_cutoff:
            overtime_hours = (end_datetime - overtime_cutoff).total_seconds() / 3600
    
    # 计算当日薪资
    if name not in employee_salary:
        payment_each_date = "Name Not Listed"
    else:
        salary_info = employee_salary[name]
        if hours_worked == 8:
            if overtime_hours > 0:
                payment_each_date = salary_info['daily'] + overtime_hours * salary_info['overtime_rate']
            else:
                payment_each_date = salary_info['daily']
        else:
            if group['Time'].min() > late_limit:
                payment_each_date = salary_info['daily'] / 2
            else:
                payment_each_date = salary_info['daily']
    
    # 更新DataFrame
    df.loc[(df['Nama'] == name) & (df['Date'] == date), 'Hours Worked'] = hours_worked
    df.loc[(df['Nama'] == name) & (df['Date'] == date), 'Overtime Hours'] = overtime_hours
    df.loc[(df['Nama'] == name) & (df['Date'] == date), 'Payment Each Date'] = payment_each_date

# 处理节假日薪资
holiday_status = df['Tgl/Waktu'].dt.normalize().isin(pd.DatetimeIndex(holidays_date))
df = pd.merge(df, holiday_status.to_frame('Holiday'), left_index=True, right_index=True)

# 节假日薪资计算：仅对有效数值进行运算
mask = (df['Holiday'] == True) & (df['Payment Each Date'] != "Name Not Listed")
df.loc[mask, 'Payment Each Date'] = df.loc[mask, 'Payment Each Date'] * 1.5 + 5000

# 计算总薪资
df_total = df.groupby(['Nama', 'Date'])['Payment Each Date'].max().groupby('Nama').sum().rename('Total Payment')
df = df.merge(df_total, how='left', on='Nama')

# 打印结果并保存
print(df)
df.to_excel(excel_writer='/content/drive/MyDrive/Colab Notebooks/test.xlsx', index=False)

额外优化说明 #

• 使用字典存储员工薪资，减少冗余的条件判断，后续新增员工只需修改字典
• 增加跨天考勤处理，覆盖更多场景
• 对节假日薪资计算增加判断，避免对字符串类型进行运算
• 初始化变量，避免未定义错误

内容的提问来源于stack exchange，提问作者Akazadi