PowerShell CSV考勤统计异常求助：总天数为空及缺勤规则需求

问题分析 #

当前脚本存在三个关键问题：

1. TotalDays列为空：日期范围计算逻辑错误，未正确获取当月首日和考勤记录的最晚日期
2. WeekEnds统计值为0：周末判断逻辑失效，无法识别周六周日
3. 未实现3次迟到转1次缺勤规则：缺少迟到次数统计及转换逻辑

核心修复步骤 #

1. 修复TotalDays计算 #

通过Get-Date获取当月第一天，从CSV中提取最晚记录日期，计算两者的天数差（包含首尾日期）：

# 获取当月第一天
$monthStart = (Get-Date).Date.AddDays(-((Get-Date).Day - 1))
# 提取考勤记录中的最晚日期并转换为DateTime类型
$latestRecordDate = [DateTime]($csvData | Sort-Object Date -Descending | Select-Object -First 1).Date
# 计算总天数
$totalDays = ($latestRecordDate - $monthStart).Days + 1

2. 修复周末统计 #

遍历日期范围，判断每个日期的DayOfWeek属性是否为周六或周日：

$weekEndCount = 0
for ($date = $monthStart; $date -le $latestRecordDate; $date = $date.AddDays(1)) {
    if ($date.DayOfWeek -in 'Saturday', 'Sunday') {
        $weekEndCount++
    }
}

3. 实现迟到转缺勤规则 #

按员工分组统计迟到次数，计算可转换的缺勤数（迟到次数整除3），再调整最终出勤天数：

$employeeStats = $csvData | Group-Object EmployeeName | ForEach-Object {
    $lateCount = ($_.Group | Where-Object Status -eq '迟到').Count
    $convertedAbsence = [math]::Floor($lateCount / 3)
    # 原始出勤天数减去转换的缺勤次数，得到最终出勤天数
    $finalAttendance = ($_.Group | Where-Object Status -eq '出勤').Count - $convertedAbsence

    [PSCustomObject]@{
        EmployeeName       = $_.Name
        TotalDays          = $totalDays
        WeekEnds           = $weekEndCount
        LateCount          = $lateCount
        ConvertedAbsence   = $convertedAbsence
        AttendanceDays     = $finalAttendance
    }
}

完整修正脚本 #

假设原始CSV路径为./attendance.csv，完整脚本如下：

# 导入CSV考勤数据
$csvData = Import-Csv -Path "./attendance.csv"

# 计算当月1日至最晚记录日的总天数
$monthStart = (Get-Date).Date.AddDays(-((Get-Date).Day - 1))
$latestRecordDate = [DateTime]($csvData | Sort-Object Date -Descending | Select-Object -First 1).Date
$totalDays = ($latestRecordDate - $monthStart).Days + 1

# 统计日期范围内的周末天数
$weekEndCount = 0
for ($date = $monthStart; $date -le $latestRecordDate; $date = $date.AddDays(1)) {
    if ($date.DayOfWeek -in 'Saturday', 'Sunday') {
        $weekEndCount++
    }
}

# 按员工统计并应用迟到转缺勤规则
$employeeStats = $csvData | Group-Object EmployeeName | ForEach-Object {
    $employeeRecords = $_.Group
    $lateCount = ($employeeRecords | Where-Object { $_.Status -eq '迟到' }).Count
    $convertedAbsence = [math]::Floor($lateCount / 3)
    $attendanceDays = ($employeeRecords | Where-Object { $_.Status -eq '出勤' }).Count - $convertedAbsence

    [PSCustomObject]@{
        EmployeeName       = $_.Name
        TotalDays          = $totalDays
        WeekEnds           = $weekEndCount
        LateCount          = $lateCount
        ConvertedAbsence   = $convertedAbsence
        AttendanceDays     = $attendanceDays
    }
}

# 输出统计结果
$employeeStats | Format-Table -AutoSize
# 可选：导出到新CSV文件
# $employeeStats | Export-Csv -Path "./attendance_report.csv" -NoTypeInformation

关键说明 #

• 确保CSV中的Date列是可转换为DateTime的格式（如yyyy-MM-dd），若格式不符需添加[DateTime]::ParseExact()进行强制转换
• 若CSV中状态字段的名称或值不同（如英文Late/Present），需对应修改Where-Object中的判断条件
• 总天数计算包含当月首日和最晚记录日，符合考勤统计的常规逻辑

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