Excel考勤数据匹配：合并签到签出表统计每日工时

Hey there! Let's work through this employee time-tracking challenge—you’re already thinking in the right direction with combining ID and Date for matching, but let’s refine the approach to make it reliable, efficient, and easy to maintain.

首选方案：Power Query（Get & Transform） #

If you're using Excel 2016 or later, Power Query is hands down the best tool for this job. It handles data merging, cleaning, and transformation way more flexibly than worksheet functions, and it’s repeatable if your data updates regularly. Here’s how to set it up:

1. Import both sheets into Power Query

   • Go to the Data tab → Get Data → From File → From Workbook
   • Select your workbook, then choose both the "Sign-In" and "Sign-Out" sheets to load into the Power Query Editor

2. Clean up and rename fields

   • For the Sign-In table, rename the Time column to Sign-In Time (right-click the column header → Rename)
   • Do the same for the Sign-Out table: rename Time to Sign-Out Time
   • Double-check that both tables have matching Last Name, First Name, ID, and Date fields (no typos!)

3. Merge the tables

   • Select the Sign-In table in the Power Query Editor
   • Click Merge Queries → Merge as New
   • In the merge window:
     • Choose "Sign-Out" as the second table
     • Select ID and Date as the matching columns (hold Ctrl to select multiple)
     • Set the join kind to Left Outer (this keeps all Sign-In records, even if there’s no matching Sign-Out)
   • Click OK

4. Expand the merged data

   • Click the expand icon (🔽) on the new "Sign-Out" column
   • Only check the Sign-Out Time box, then click OK

5. Calculate total hours worked

   • Click Add Column → Custom Column
   • Use this formula to calculate hours (and handle missing sign-outs):

     = if [Sign-Out Time] = null then "Missing Sign-Out" else Duration.TotalHours([Sign-Out Time] - [Sign-In Time])
     

   • If you want to format missing entries as 0 instead of text, adjust the formula to:

     = if [Sign-Out Time] = null then 0 else Duration.TotalHours([Sign-Out Time] - [Sign-In Time])
     

6. Load back to Excel

   • Click Close & Load to bring the merged, calculated table into a new worksheet. You can refresh this table anytime your Sign-In/Sign-Out data updates!

备选方案：优化后的工作表函数 #

If you prefer sticking to cell functions instead of Power Query, here’s how to refine your original INDEX/MATCH approach (with modern functions like XLOOKUP for simplicity):

1. Create a unique match key

   • In the Sign-In sheet, add a helper column UniqueKey with this formula:

     = [@ID] & "|" & [@Date]
     

   • Repeat this for the Sign-Out sheet (same column name and formula)

2. Pull in matching sign-out times

   • In the Sign-In sheet, add a Sign-Out Time column with XLOOKUP (works in Excel 365/2021):

     = XLOOKUP([@UniqueKey], Sign-Out[UniqueKey], Sign-Out[Time], "No Sign-Out", 0)
     

   • If you’re on an older Excel version, use INDEX/MATCH:

     = IFERROR(INDEX(Sign-Out[Time], MATCH([@UniqueKey], Sign-Out[UniqueKey], 0)), "No Sign-Out")
     

3. Calculate daily hours

   • Add a Hours Worked column with this formula (converts Excel time difference from days to hours, and handles missing sign-outs):

     = IF([@[Sign-Out Time]] = "No Sign-Out", "Missing", ([@[Sign-Out Time]] - [@Time])*24)
     

处理特殊情况 #

Don’t forget edge cases that can break your calculations:

• Multiple sign-ins/sign-outs in one day: Use MINIFS to get the earliest sign-in and MAXIFS to get the latest sign-out:

  = MINIFS(Sign-In[Time], Sign-In[ID], [@ID], Sign-In[Date], [@Date])
  = MAXIFS(Sign-Out[Time], Sign-Out[ID], [@ID], Sign-Out[Date], [@Date])
  

• Cross-day sign-outs: If an employee signs out the next day, adjust the hour calculation to account for the 24-hour wrap:

  = IF([@[Sign-Out Time]] < [@Time], ([@[Sign-Out Time]] + 1 - [@Time])*24, ([@[Sign-Out Time]] - [@Time])*24)
  

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