EXCEL如果是下午12点30分之后，跟11点30分之前的，直接输出结果 比如8点开始到下午16点多结果的
发布网友
发布时间：2022-05-01 15:55
共2个回答
热心网友
时间：2023-10-22 15:09
公式如下：=MID(IF(OR(TEXT(B1,"h:mm")-"12:30">0,TEXT(D1,"h:mm")-"11:30"<0),TEXT(TEXT(D1,"h:mm")-TEXT(B1,"h:mm"),"h:mm"),IF(AND(TEXT(B1,"h:mm")-"11:30">0,TEXT(D1,"h:mm")-"12:30"<0),"0:00",IF(AND(TEXT(B1,"h:mm")-"11:30"<0,TEXT(D1,"h:mm")-"12:30"<0,TEXT(D1,"h:mm")-"11:30">0),TEXT("11:30"-TEXT(B1,"h:mm"),"h:mm"),IF(AND(TEXT(B1,"h:mm")-"11:30">0,TEXT(B1,"h:mm")-"12:30"<0,TEXT(D1,"h:mm")-"12:30">0),TEXT(TEXT(D1,"h:mm")-"12:30","h:mm"),TEXT(TEXT(D1,"h:mm")-TEXT(B1,"h:mm")-"1:00","h:mm"))))),1,FIND(":",IF(OR(TEXT(B1,"h:mm")-"12:30">0,TEXT(D1,"h:mm")-"11:30"<0),TEXT(TEXT(D1,"h:mm")-TEXT(B1,"h:mm"),"h:mm"),IF(AND(TEXT(B1,"h:mm")-"11:30">0,TEXT(D1,"h:mm")-"12:30"<0),"0:00",IF(AND(TEXT(B1,"h:mm")-"11:30"<0,TEXT(D1,"h:mm")-"12:30"<0,TEXT(D1,"h:mm")-"11:30">0),TEXT("11:30"-TEXT(B1,"h:mm"),"h:mm"),IF(AND(TEXT(B1,"h:mm")-"11:30">0,TEXT(B1,"h:mm")-"12:30"<0,TEXT(D1,"h:mm")-"12:30">0),TEXT(TEXT(D1,"h:mm")-"12:30","h:mm"),TEXT(TEXT(D1,"h:mm")-TEXT(B1,"h:mm")-"1:00","h:mm"))))))-1)+MID(IF(OR(TEXT(B1,"h:mm")-"12:30">0,TEXT(D1,"h:mm")-"11:30"<0),TEXT(TEXT(D1,"h:mm")-TEXT(B1,"h:mm"),"h:mm"),IF(AND(TEXT(B1,"h:mm")-"11:30">0,TEXT(D1,"h:mm")-"12:30"<0),"0:00",IF(AND(TEXT(B1,"h:mm")-"11:30"<0,TEXT(D1,"h:mm")-"12:30"<0,TEXT(D1,"h:mm")-"11:30">0),TEXT("11:30"-TEXT(B1,"h:mm"),"h:mm"),IF(AND(TEXT(B1,"h:mm")-"11:30">0,TEXT(B1,"h:mm")-"12:30"<0,TEXT(D1,"h:mm")-"12:30">0),TEXT(TEXT(D1,"h:mm")-"12:30","h:mm"),TEXT(TEXT(D1,"h:mm")-TEXT(B1,"h:mm")-"1:00","h:mm"))))),FIND(":",IF(OR(TEXT(B1,"h:mm")-"12:30">0,TEXT(D1,"h:mm")-"11:30"<0),TEXT(TEXT(D1,"h:mm")-TEXT(B1,"h:mm"),"h:mm"),IF(AND(TEXT(B1,"h:mm")-"11:30">0,TEXT(D1,"h:mm")-"12:30"<0),"0:00",IF(AND(TEXT(B1,"h:mm")-"11:30"<0,TEXT(D1,"h:mm")-"12:30"<0,TEXT(D1,"h:mm")-"11:30">0),TEXT("11:30"-TEXT(B1,"h:mm"),"h:mm"),IF(AND(TEXT(B1,"h:mm")-"11:30">0,TEXT(B1,"h:mm")-"12:30"<0,TEXT(D1,"h:mm")-"12:30">0),TEXT(TEXT(D1,"h:mm")-"12:30","h:mm"),TEXT(TEXT(D1,"h:mm")-TEXT(B1,"h:mm")-"1:00","h:mm"))))))+1,2)/60
以上供参考
热心网友
时间：2023-10-22 15:09
公式如下：=MID(IF(OR(TEXT(B1,"h:mm")-"12:30">0,TEXT(D1,"h:mm")-"11:30"<0),TEXT(TEXT(D1,"h:mm")-TEXT(B1,"h:mm"),"h:mm"),IF(AND(TEXT(B1,"h:mm")-"11:30">0,TEXT(D1,"h:mm")-"12:30"<0),"0:00",IF(AND(TEXT(B1,"h:mm")-"11:30"<0,TEXT(D1,"h:mm")-"12:30"<0,TEXT(D1,"h:mm")-"11:30">0),TEXT("11:30"-TEXT(B1,"h:mm"),"h:mm"),IF(AND(TEXT(B1,"h:mm")-"11:30">0,TEXT(B1,"h:mm")-"12:30"<0,TEXT(D1,"h:mm")-"12:30">0),TEXT(TEXT(D1,"h:mm")-"12:30","h:mm"),TEXT(TEXT(D1,"h:mm")-TEXT(B1,"h:mm")-"1:00","h:mm"))))),1,FIND(":",IF(OR(TEXT(B1,"h:mm")-"12:30">0,TEXT(D1,"h:mm")-"11:30"<0),TEXT(TEXT(D1,"h:mm")-TEXT(B1,"h:mm"),"h:mm"),IF(AND(TEXT(B1,"h:mm")-"11:30">0,TEXT(D1,"h:mm")-"12:30"<0),"0:00",IF(AND(TEXT(B1,"h:mm")-"11:30"<0,TEXT(D1,"h:mm")-"12:30"<0,TEXT(D1,"h:mm")-"11:30">0),TEXT("11:30"-TEXT(B1,"h:mm"),"h:mm"),IF(AND(TEXT(B1,"h:mm")-"11:30">0,TEXT(B1,"h:mm")-"12:30"<0,TEXT(D1,"h:mm")-"12:30">0),TEXT(TEXT(D1,"h:mm")-"12:30","h:mm"),TEXT(TEXT(D1,"h:mm")-TEXT(B1,"h:mm")-"1:00","h:mm"))))))-1)+MID(IF(OR(TEXT(B1,"h:mm")-"12:30">0,TEXT(D1,"h:mm")-"11:30"<0),TEXT(TEXT(D1,"h:mm")-TEXT(B1,"h:mm"),"h:mm"),IF(AND(TEXT(B1,"h:mm")-"11:30">0,TEXT(D1,"h:mm")-"12:30"<0),"0:00",IF(AND(TEXT(B1,"h:mm")-"11:30"<0,TEXT(D1,"h:mm")-"12:30"<0,TEXT(D1,"h:mm")-"11:30">0),TEXT("11:30"-TEXT(B1,"h:mm"),"h:mm"),IF(AND(TEXT(B1,"h:mm")-"11:30">0,TEXT(B1,"h:mm")-"12:30"<0,TEXT(D1,"h:mm")-"12:30">0),TEXT(TEXT(D1,"h:mm")-"12:30","h:mm"),TEXT(TEXT(D1,"h:mm")-TEXT(B1,"h:mm")-"1:00","h:mm"))))),FIND(":",IF(OR(TEXT(B1,"h:mm")-"12:30">0,TEXT(D1,"h:mm")-"11:30"<0),TEXT(TEXT(D1,"h:mm")-TEXT(B1,"h:mm"),"h:mm"),IF(AND(TEXT(B1,"h:mm")-"11:30">0,TEXT(D1,"h:mm")-"12:30"<0),"0:00",IF(AND(TEXT(B1,"h:mm")-"11:30"<0,TEXT(D1,"h:mm")-"12:30"<0,TEXT(D1,"h:mm")-"11:30">0),TEXT("11:30"-TEXT(B1,"h:mm"),"h:mm"),IF(AND(TEXT(B1,"h:mm")-"11:30">0,TEXT(B1,"h:mm")-"12:30"<0,TEXT(D1,"h:mm")-"12:30">0),TEXT(TEXT(D1,"h:mm")-"12:30","h:mm"),TEXT(TEXT(D1,"h:mm")-TEXT(B1,"h:mm")-"1:00","h:mm"))))))+1,2)/60
以上供参考
热心网友
时间：2023-10-22 15:09
热心网友
时间：2023-10-22 15:09
热心网友
时间：2023-10-22 15:09
公式如下：=MID(IF(OR(TEXT(B1,"h:mm")-"12:30">0,TEXT(D1,"h:mm")-"11:30"<0),TEXT(TEXT(D1,"h:mm")-TEXT(B1,"h:mm"),"h:mm"),IF(AND(TEXT(B1,"h:mm")-"11:30">0,TEXT(D1,"h:mm")-"12:30"<0),"0:00",IF(AND(TEXT(B1,"h:mm")-"11:30"<0,TEXT(D1,"h:mm")-"12:30"<0,TEXT(D1,"h:mm")-"11:30">0),TEXT("11:30"-TEXT(B1,"h:mm"),"h:mm"),IF(AND(TEXT(B1,"h:mm")-"11:30">0,TEXT(B1,"h:mm")-"12:30"<0,TEXT(D1,"h:mm")-"12:30">0),TEXT(TEXT(D1,"h:mm")-"12:30","h:mm"),TEXT(TEXT(D1,"h:mm")-TEXT(B1,"h:mm")-"1:00","h:mm"))))),1,FIND(":",IF(OR(TEXT(B1,"h:mm")-"12:30">0,TEXT(D1,"h:mm")-"11:30"<0),TEXT(TEXT(D1,"h:mm")-TEXT(B1,"h:mm"),"h:mm"),IF(AND(TEXT(B1,"h:mm")-"11:30">0,TEXT(D1,"h:mm")-"12:30"<0),"0:00",IF(AND(TEXT(B1,"h:mm")-"11:30"<0,TEXT(D1,"h:mm")-"12:30"<0,TEXT(D1,"h:mm")-"11:30">0),TEXT("11:30"-TEXT(B1,"h:mm"),"h:mm"),IF(AND(TEXT(B1,"h:mm")-"11:30">0,TEXT(B1,"h:mm")-"12:30"<0,TEXT(D1,"h:mm")-"12:30">0),TEXT(TEXT(D1,"h:mm")-"12:30","h:mm"),TEXT(TEXT(D1,"h:mm")-TEXT(B1,"h:mm")-"1:00","h:mm"))))))-1)+MID(IF(OR(TEXT(B1,"h:mm")-"12:30">0,TEXT(D1,"h:mm")-"11:30"<0),TEXT(TEXT(D1,"h:mm")-TEXT(B1,"h:mm"),"h:mm"),IF(AND(TEXT(B1,"h:mm")-"11:30">0,TEXT(D1,"h:mm")-"12:30"<0),"0:00",IF(AND(TEXT(B1,"h:mm")-"11:30"<0,TEXT(D1,"h:mm")-"12:30"<0,TEXT(D1,"h:mm")-"11:30">0),TEXT("11:30"-TEXT(B1,"h:mm"),"h:mm"),IF(AND(TEXT(B1,"h:mm")-"11:30">0,TEXT(B1,"h:mm")-"12:30"<0,TEXT(D1,"h:mm")-"12:30">0),TEXT(TEXT(D1,"h:mm")-"12:30","h:mm"),TEXT(TEXT(D1,"h:mm")-TEXT(B1,"h:mm")-"1:00","h:mm"))))),FIND(":",IF(OR(TEXT(B1,"h:mm")-"12:30">0,TEXT(D1,"h:mm")-"11:30"<0),TEXT(TEXT(D1,"h:mm")-TEXT(B1,"h:mm"),"h:mm"),IF(AND(TEXT(B1,"h:mm")-"11:30">0,TEXT(D1,"h:mm")-"12:30"<0),"0:00",IF(AND(TEXT(B1,"h:mm")-"11:30"<0,TEXT(D1,"h:mm")-"12:30"<0,TEXT(D1,"h:mm")-"11:30">0),TEXT("11:30"-TEXT(B1,"h:mm"),"h:mm"),IF(AND(TEXT(B1,"h:mm")-"11:30">0,TEXT(B1,"h:mm")-"12:30"<0,TEXT(D1,"h:mm")-"12:30">0),TEXT(TEXT(D1,"h:mm")-"12:30","h:mm"),TEXT(TEXT(D1,"h:mm")-TEXT(B1,"h:mm")-"1:00","h:mm"))))))+1,2)/60
以上供参考
热心网友
时间：2023-10-22 15:09
