PowerShell统计员工迟到早退时LateBy/EarlyBy字段值异常求助

问题背景 #

现有两份考勤相关文件：

1. 考勤记录CSV（kith.csv）：

Date    Person  Start   End Elapsed
04/03/2023  Tehreem     7:29:43 13:35:27    06h, 05m, 44s
04/03/2023  Irfan   7:59:36 13:34:53    05h, 35m, 17s
04/03/2023  Fatima      7:40:06 13:31:06    05h, 51m, 00s
04/03/2023  Talat   7:43:18 13:17:37    05h, 34m, 19s
04/03/2023  Natasha     7:50:37 13:31:16    05h, 40m, 39s
04/03/2023  Zelma   7:46:18 13:40:52    05h, 54m, 34s
04/03/2023  Maryam  7:42:27 13:30:48    05h, 48m, 21s
04/03/2023  Sania   7:38:31 13:32:21    05h, 53m, 50s
04/03/2023  Qurat   8:00:23 13:31:09    05h, 30m, 46s
04/03/2023  Aliza   7:41:45 13:30:53    05h, 49m, 08s
04/03/2023  Nusrat  7:22:09 13:30:59    06h, 08m, 50s

2. 员工排班XLSX（timetable.xlsx）：

Person  TimeIn  TimeOut
-----------------------
Tehreem 7:00 AM 2:30 PM
Aliza   7:00 AM 2:30 PM
Ambreen 7:00 AM 2:30 PM
Amna    7:00 AM 2:30 PM
Arif    7:00 AM 2:30 PM
maryam  7:00 AM 2:30 PM
moona   7:00 AM 2:30 PM

编写PowerShell脚本计算迟到/早退时长后，结果中LateBy和EarlyBy字段显示为hours, minutes, seconds而非正确时长。

问题原因 #

1. 时间格式解析错误：考勤CSV的Start/End字段格式为h:mm:ss（包含秒），但脚本中用h:mm tt（12小时制带AM/PM）解析，导致解析失败，$start/$end取默认DateTime值，时间差计算失效。
2. 人名大小写不匹配：排班表中人名是小写maryam，但考勤记录中是大写Maryam，导致匹配不到排班数据，$timeIn/$timeOut为空，计算时出现无效值。
3. 空值未处理：未判断$timeIn/$timeOut是否为空，直接进行时间比较，引发无效计算。

修正后的脚本 #

# Load the attendance records from the CSV file
$attendanceRecords = Import-Csv -Path "F:\att new\kith1.csv"

# Load the employee timetable from the XLSX file
$employeeTimetable = Import-Excel -Path "F:\att new\timetable.xlsx"

# Create an empty array to store the late comings and early goings
$results = @()

# Loop through each attendance record
foreach ($record in $attendanceRecords) {
    $person = $record.Person
    # 修正：解析带秒的24小时制时间格式
    $start = [DateTime]::ParseExact($record.Start, "H:mm:ss", $null)
    $end = [DateTime]::ParseExact($record.End, "H:mm:ss", $null)

    # 修正：忽略大小写匹配人名，避免大小写不一致导致的匹配失败
    $timetable = $employeeTimetable | Where-Object { $_.Person -eq $person -or $_.Person -eq $person.ToLower() }
    
    $timeIn = $null
    $timeOut = $null
    if ($timetable) {
        $timeIn = [Datetime]::ParseExact($timetable.TimeIn, "h:mm tt", $null)
        $timeOut = [Datetime]::ParseExact($timetable.TimeOut, "h:mm tt", $null)
    }

    # Check if the employee was late
    $lateBy = ""
    if ($start -and $timeIn -and $start -gt $timeIn) {
        # 格式化时间差为hh:mm:ss格式，保留秒数
        $lateTime = ($start - $timeIn).ToString("hh\:mm\:ss")
        $lateBy = $lateTime
    }

    # Check if the employee left early
    $earlyBy = ""
    if ($end -and $timeOut -and $end -lt $timeOut) {
        $earlyTime = ($timeOut - $end).ToString("hh\:mm\:ss")
        $earlyBy = $earlyTime
    }

    # Add the attendance record to the results
    $attendanceRecord = [PSCustomObject]@{
        Date = $record.Date
        Person = $person
        Start = $record.Start
        End = $record.End
        Elapsed = $record.Elapsed
        LateBy = $lateBy
        EarlyBy = $earlyBy
    }

    $results += $attendanceRecord
}

# Sort the results by date and person
$results = $results | Sort-Object -Property Date, Person

# Export the results to a CSV file
$results | Export-Csv -Path "F:\att new\complete1.csv" -NoTypeInformation

关键修改说明 #

• 时间解析：将Start/End的解析格式改为H:mm:ss（24小时制带秒），完美匹配CSV中的时间格式。
• 人名匹配：添加-or $_.Person -eq $person.ToLower()逻辑，忽略大小写匹配排班数据，解决大小写不一致问题。
• 空值处理：增加$start -and $timeIn这类判断，避免空值导致的计算错误，确保只有有效数据才进行时长计算。
• 时间差格式化：用hh\:mm\:ss格式保留秒数，同时转义冒号避免被解析为时间分隔符，确保格式统一。

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