我想知道如果每个时间块都采用一个单元格的格式，Excel 是否可以计算时间跨度5a-1p。这样我就可以添加每周的小时数，而不用做太多更改。
样本数据：
Name Mon Tues Wed Thur Fri Sat Sun Total Hours
Bill 2a-10p 5a-1p 730a-330p OFF OFF 5p-12a 5a-10a
谢谢你！
答案1
以下公式将给出一天时间跨度内的小时数。它的工作原理是解析文本，并以 Excel 识别的时间格式重新格式化开始和结束时间。完成后，只需减去并将结果转换为小时数即可。
=IF(B2="OFF",0,((IF(LEN(RIGHT(B2,LEN(B2)-FIND("-",B2)))>3,LEFT(RIGHT(B2,LEN(B2)-FIND("-",B2)),LEN(RIGHT(B2,LEN(B2)-FIND("-",B2)))-3)&":"&MID(RIGHT(B2,LEN(B2)-FIND("-",B2)),LEN(RIGHT(B2,LEN(B2)-FIND("-",B2)))-2,2),LEFT(RIGHT(B2,LEN(B2)-FIND("-",B2)),LEN(RIGHT(B2,LEN(B2)-FIND("-",B2)))-1))&" "&RIGHT(RIGHT(B2,LEN(B2)-FIND("-",B2)),1)&"m")-(IF(LEN(LEFT(B2,FIND("-",B2)-1))>3,LEFT(LEFT(B2,FIND("-",B2)-1),LEN(LEFT(B2,FIND("-",B2)-1))-3)&":"&MID(LEFT(B2,FIND("-",B2)-1),LEN(LEFT(B2,FIND("-",B2)-1))-2,2),LEFT(LEFT(B2,FIND("-",B2)-1),LEN(LEFT(B2,FIND("-",B2)-1))-1))&" "&RIGHT(LEFT(B2,FIND("-",B2)-1),1)&"m"))*24+IF(AND(MID(B2,FIND("-",B2)-1,1)="p",RIGHT(B2,1)="a"),24,0))
具体来说：
RIGHT(B2,LEN(B2)-FIND("-",B2))是您的格式的结束时间。
LEFT(B2,FIND("-",B2)-1)是您的格式的开始时间。
用End和替换这些值Start将有助于阐明公式的作用。
=IF(B2="OFF",0,((IF(LEN(End)>3,LEFT(End,LEN(End)-3)&":"&MID(End,LEN(End)-2,2),LEFT(End,LEN(End)-1))&" "&RIGHT(End,1)&"m")-(IF(LEN(Start)>3,LEFT(Start,LEN(Start)-3)&":"&MID(Start,LEN(Start)-2,2),LEFT(Start,LEN(Start)-1))&" "&RIGHT(Start,1)&"m"))*24+IF(AND(MID(B2,FIND("-",B2)-1,1)="p",RIGHT(B2,1)="a"),24,0))
这基本上意味着，如果 B2 为 ，则返回 0 OFF。
否则，格式化时间，以便 Excel 将其识别为时间值。
为此，首先检查时间中字母（a 或 p）前面有多少位数字。
如果数字超过 2 位，则时间包含分钟，因此需要在小时和分钟之间使用冒号进行格式化。否则，不执行任何操作。
然后，在数字和字母之间留一个空格，并m在字母后面粘贴一个（使其成为am或pm）。
完成每个操作后，从结束时间中减去开始时间。这将返回时间跨度中一天的分数，因此将其乘以 24 可转换为小时。
如果结束时间为上午，开始时间为下午，则结果为负数。为了纠正这种情况，公式会检查此条件，如果满足条件，则将结果加 24。
答案2
您还可以使用较短的版本：
=IF(B2="OFF",0,ABS(MOD(24*MMULT(LARGE(IFERROR(0+REPLACE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"a",":00 AM"),"p",":00 PM"),"-",REPT(" ",99)),99*{0,1}+1,99)),{2;3},,":00"),0),{1,2}),{1;-1}),24)-24*(IFERROR(MMULT(FIND({"a","p"},B2),{-1;1})<0,0))))
问候