Question

请教高手 excel如何计算时间差多少小时 除去18:00-8:00

如果派单时间在18:00-8:00，从八点以后开始计时如果结束时间在18:00-8:00，也从八点以后开始计时计算派单到结束之间的历时（小时）

Answer 1

=IF(TEXT(A2,"hhmmss")>TEXT("18:0:0","hhmmss"),TEXT(A2+1,"yyyy/mm/dd")&" 08:00:00",IF(TEXT(A2,"hhmmss")<TEXT("08:0:0","hhmmss"),TEXT(A2,"yyyy/mm/dd")&" 08:00:00",A2))

=IF(TEXT(B2,"hhmmss")>TEXT("18:0:0","hhmmss"),TEXT(B2+1,"yyyy/mm/dd")&" 08:00:00",IF(TEXT(B2,"hhmmss")<TEXT("08:0:0","hhmmss"),TEXT(B2,"yyyy/mm/dd")&" 08:00:00",B2))

=ROUND((D2-C2)*24,1)

如果表格格式不是明确固定的话，我觉得加两个辅助列简单点，不看也可以隐藏

如果不要辅助列用if嵌套的话，要写好长好长....