excel 统计加班时长

 天天加班，每月还要写工作计量表和上下班时间，顺便统计下每天和每月的加班时长。

 公司规定上班时间8:30  ，下班时间17:00； 17:00 -20:00 不算加班。

开始使用的公式：（比较复杂，无法使用sum 统计）
=TEXT(IF(AND(TEXT(J14,"HH:MM")>"20:00",TEXT(J14,"HH:MM")<"24:00"),TEXT(J14,"HH:MM")-"20:00",IF(TEXT(J14,"HH:MM")="00:00","00:00",IF(AND(TEXT(J14,"HH:MM")<"08:00",TEXT(J14,"HH:MM")>"00:00"),"04:00"+TEXT(J14,"HH:MM"),0))),"HH:MM")
	
优化后的公式：（需要设置单元格格式为自定义：[h]:m）	
=IF(J14<>"",IF(AND(J14<=TIME(23,59,59),J14>TIME(20,0,0)),J14-TIME(20,0,0),if(and(J14>=TIME(0,0,0),J14<TIME(8,30,0)),time(23,59,59)-time(20,0,0)+j14,TIME(0,0,0))),TIME(0,0,0))

发现周六日加班不能全天算加班时，在优化一版，周六日单独算加班时长
=IF(J14<>"",IF(OR(WEEKDAY($C$9&-A14,2)=6,WEEKDAY($C$9&-A14,2)=7),J14-I14,IF(AND(J14<=TIME(23,59,59),J14>TIME(20,0,0)),J14-TIME(20,0,0),IF(AND(J14>=TIME(0,0,0),J14<TIME(8,30,0)),TIME(23,59,59)-TIME(20,0,0)+J14,TIME(0,0,0)))),TIME(0,0,0))

兼顾周六日超过零晨
=IF(J14<>"",IF(OR(WEEKDAY($C$9&-A14,2)=6,WEEKDAY($C$9&-A14,2)=7),IF(AND(J14>=TIME(0,0,0),J14<TIME(8,30,0)),TIME(23,59,59)-I14+J14,J14-I14),IF(AND(J14<=TIME(23,59,59),J14>TIME(20,0,0)),J14-TIME(20,0,0),IF(AND(J14>=TIME(0,0,0),J14<TIME(8,30,0)),TIME(23,59,59)-TIME(20,0,0)+J14,TIME(0,0,0)))),TIME(0,0,0))

=MINUTE(TIMEVALUE(TEXT(P20,"h:mm")))