工单各团队域停留时长计算需求：支持Excel、书签工具及jQuery脚本

需求说明 #

计算工单在各团队的总停留时长，规则如下：

• 工单被分配到团队（Assigned状态）时开始计时，转派到其他团队（Referred状态）时结束计时
• 若工单后续再次返回同一团队，停留时长累计计算
• 最终按总停留时长从长到短排序，输出格式示例：

  Central Team Office = 13H:59M
  Network Group 1 = 1H:35M
  SOC Cable team = 0H:25M
  

实现方法 #

方法一：Excel表格处理 #

适合手动处理单条工单数据，步骤如下：

1. 将CSV数据导入Excel，把Action time列设置为日期时间格式
2. 添加辅助列：
   • 当前团队列：公式=IF(B2="Assigned",G2,"")，提取每次分配动作对应的团队（User Group列）
   • 离开时间列：公式=XLOOKUP(A2,$A$3:$A$50,$A$3:$A$50,"",-1)，匹配当前分配动作对应的下一个转派动作时间（数据范围根据实际行数调整）
3. 计算单次停留时长：公式=IF(I2<>"",J2-A2,0)，用离开时间减去到达时间
4. 累计团队总时长：用SUMIF函数，比如=SUMIF($I$2:$I$50,K2,$L$2:$L$50)（K列是整理好的团队列表）
5. 转换为H:M格式：公式=TEXT(M2,"[H]H:MM")，最后按总时长降序排序即可

方法二：Bookmarklet书签工具 #

适合在浏览器中快速计算，无需安装软件：

1. 新建浏览器书签，将以下代码复制到书签的URL栏：

   javascript:(function(){
     const csv = prompt('粘贴CSV数据：');
     if(!csv) return;
     const rows = csv.split('\n').filter(r=>r.trim());
     const headers = rows[0].split(',');
     const timeIdx = headers.indexOf('Action time');
     const statusIdx = headers.indexOf('Status');
     const groupIdx = headers.indexOf('User Group');
     
     const durations = {};
     let currTeam = null;
     let start = null;
     
     // 反转数据，按时间从旧到新处理
     const sortedRows = rows.slice(1).reverse().map(row=>{
       const cols = row.split(',');
       return {
         time: new Date(cols[timeIdx]),
         status: cols[statusIdx],
         group: cols[groupIdx]
       };
     });
     
     sortedRows.forEach(row=>{
       if(row.status==='Assigned'){
         currTeam = row.group;
         start = row.time;
       } else if(row.status==='Referred' && currTeam){
         const diff = row.time - start;
         durations[currTeam] = (durations[currTeam] || 0) + diff;
         currTeam = null;
         start = null;
       }
     });
     
     const result = Object.entries(durations)
       .map(([t,ms])=>{
         const h = Math.floor(ms/3600000);
         const m = Math.floor((ms%3600000)/60000);
         return `${t} = ${h}H:${m.toString().padStart(2,'0')}M`;
       })
       .sort((a,b)=>{
         const aMin = parseInt(a.match(/(\d+)H/)[1])*60 + parseInt(a.match(/(\d+)M/)[1]);
         const bMin = parseInt(b.match(/(\d+)H/)[1])*60 + parseInt(b.match(/(\d+)M/)[1]);
         return bMin - aMin;
       });
     
     alert(result.join('\n'));
   })();
   

2. 使用时点击书签，粘贴CSV数据，弹窗会直接显示排序后的计算结果

方法三：jQuery脚本（网页端处理） #

适合直接在工单系统页面计算历史数据：

1. 打开工单详情页，按下F12打开开发者工具，切换到Console标签
2. 粘贴以下代码并回车执行：

   $(function(){
     const durations = {};
     let currTeam = null;
     let startTime = null;
     
     // 假设工单历史表格选择器为#ticket-history，可根据实际页面调整
     $('#ticket-history tr').each(function(){
       const $tds = $(this).find('td');
       const timeStr = $tds.eq(0).text().trim();
       const status = $tds.eq(1).text().trim();
       const group = $tds.eq(6).text().trim();
       
       if(!timeStr || !status) return;
       
       const actionTime = new Date(timeStr);
       if(status==='Assigned'){
         currTeam = group;
         startTime = actionTime;
       } else if(status==='Referred' && currTeam){
         const diff = actionTime - startTime;
         durations[currTeam] = (durations[currTeam] || 0) + diff;
         currTeam = null;
         startTime = null;
       }
     });
     
     const sorted = Object.entries(durations)
       .map(([t,ms])=>{
         const h = Math.floor(ms/3600000);
         const m = Math.floor((ms%3600000)/60000);
         return `${t} = ${h}H:${m.toString().padStart(2,'0')}M`;
       })
       .sort((a,b)=>{
         const aMin = parseInt(a.match(/(\d+)H/)[1])*60 + parseInt(a.match(/(\d+)M/)[1]);
         const bMin = parseInt(b.match(/(\d+)H/)[1])*60 + parseInt(b.match(/(\d+)M/)[1]);
         return bMin - aMin;
       });
     
     console.log(sorted.join('\n'));
     $('body').append('<pre style="background:#f5f5f5;padding:10px;">'+sorted.join('\n')+'</pre>');
   });
   

3. 页面会追加显示计算结果，控制台也会同步输出

样本CSV数据 #

Action time,Status,RC,Referal Group,Assignee,User,User Group
8/16/2022 21:39,Assigned,,Network Group 1,,,Network Group 1
8/16/2022 21:39,Referred,,Network Group 1,,,Network Group 2
8/16/2022 21:35,Assigned,,Network Group 2,,,Network Group 2
8/16/2022 21:33,Referred,,Network Group 2,,,Network Group 1
8/16/2022 21:32,Assigned,,Network Group 1,,,Network Group 1
8/16/2022 21:31,Referred,,Network Group 1,,,Central Team Office
8/16/2022 21:29,Assigned,,Central Team Office,,,Central Team Office
8/16/2022 21:16,Referred,,Central Team Office,,,SOC Cable team
8/16/2022 21:15,Assigned,,SOC Cable team,,,SOC Cable team
8/16/2022 15:33,Referred,,SOC Cable team,,,Contractor Cable team
8/16/2022 15:29,Assigned,,Central Team Office,,,Central Team Office
8/16/2022 15:27,Referred,,Central Team Office,,,Network Group 1
8/16/2022 15:26,Assigned,,Network Group 1,,,Network Group 1
8/16/2022 15:25,Referred,,Network Group 1,,,Network Group 2
8/16/2022 15:07,Assigned,,Network Group 2,,,Network Group 2
8/16/2022 15:03,Referred,,Network Group 2,,,Network Group 1
8/16/2022 15:00,Assigned,,Network Group 1,,,Network Group 1
8/16/2022 14:58,Referred,,Network Group 1,,,Central Team Office
8/16/2022 14:55,Assigned,,Central Team Office,,,Central Team Office
8/16/2022 14:48,Referred,,Central Team Office,,,Network Group 1
8/16/2022 14:44,Assigned,,Network Group 1,,,Network Group 1
8/16/2022 14:42,Referred,,Network Group 1,,,Network Group 2
8/16/2022 14:37,Assigned,,Network Group 2,,,Network Group 2
8/16/2022 14:31,Referred,,Network Group 2,,,Network Group 1
8/16/2022 14:29,Assigned,,Network Group 1,,,Network Group 1
8/16/2022 14:28,Referred,,Network Group 1,,,Central Team Office
8/16/2022 14:26,Assigned,,Central Team Office,,,Central Team Office
8/16/2022 14:20,Referred,,Central Team Office,,,SOC Cable team
8/16/2022 14:00,Assigned,,SOC Cable team,,,SOC Cable team
8/16/2022 4:36,Assigned,,SOC Cable team,,,SOC Cable team
8/16/2022 4:14,Referred,,SOC Cable team,,,Central Team Office
8/16/2022 4:04,Assigned,,Central Team Office,,,Central Team Office
8/16/2022 4:02,Referred,,Central Team Office,,,View team
8/16/2022 4:00,Assigned,,Network Group 1,,,View team
8/16/2022 3:58,Referred,,Network Group 1,,,Central Team Office
8/16/2022 3:50,Assigned,,Central Team Office,,,Central Team Office
8/16/2022 3:46,Referred,,Central Team Office,,,View team
8/16/2022 3:45,Assigned,,Network Group 1,,,View team
8/16/2022 3:43,Referred,,Network Group 1,,,Network Group 2
8/16/2022 3:42,Assigned,,Network Group 2,,,Network Group 2
8/16/2022 2:21,Referred,,Network Group 2,,,Network Group 1
8/16/2022 2:17,Assigned,,Network Group 1,,,Network Group 1
8/16/2022 2:16,Referred,,Network Group 1,,,Central Team Office
8/16/2022 2:15,Assigned,,Central Team Office,,,Central Team Office
8/16/2022 2:14,Referred,,Central Team Office,,,Contractor Cable team
8/16/2022 1:10,Assigned,,Contractor Cable team,,,Contractor Cable team
8/16/2022 1:07,Referred,,Contractor Cable team,,,Central Team Office
8/16/2022 1:03,Assigned,,Central Team Office,,,Central Team Office
8/16/2022 1:03,Referred,,Central Team Office,,,SOC Cable team
8/16/2022 1:00,Assigned,,SOC Cable team,,,SOC Cable team
8/15/2022 20:30,Assigned,,SOC Cable team,,,SOC Cable team
8/15/2022 19:16,Referred,,SOC Cable team,,,Central Team Office
8/15/2022 18:55,Assigned,,Central Team Office,,,Central Team Office
8/15/2022 18:53,Assigned,,Central Team Office,,,Central Team Office
8/15/2022 18:40,Referred,,Central Team Office,,,SOC Cable team
8/14/2022 23:19,Assigned,,SOC Cable team,,,SOC Cable team
8/14/2022 23:07,Referred,,SOC Cable team,,,Central Team Office
8/14/2022 23:06,Assigned,,Central Team Office,,,Central Team Office
8/14/2022 23:03,Referred,,Central Team Office,,,Contractor Cable team
8/14/2022 23:01,Assigned,,Contractor Cable team,,,Contractor Cable team
8/14/2022 22:59,Referred,,Contractor Cable team,,,Central Team Office

内容的提问来源于stack exchange，提问作者A.D usa