Calculating day & night shift

[Sonja]

How can I calculate numbers of hours that fall into day and night working hour.
Some time I work for shift that atarts before 6 am (6:00) and end during regular time. Night working time start at 10pm (22:00) and end at 6 am (6:00)

For total working hours I use this formula for cell D2
=(D2-C2+(C2>D2))*24

For day hours I use this for cell E2
=IF(AND(C2>0;D2>0);IF(D2<=C2;D2+"24:00";D2)-C2-MAX(MIN("6:00";IF(D2<=C2;D2+"24:00";D2))-C2;0)-MAX(MIN("30:00";IF(D2<=C2;#REF!+"24:00";D2))-MAX("22:00";C2);0);"")*24

For night hours (for F4) just substract
=D4-E4

Everything work fine when I emter time but when a cell is empty or when I enter text I have a problem.
A B C D E F
1 FROM TO TOTAL DAY NIGHT
2 05:00 15:00 10 9 1
3 #VALUE! #VALUE! #VALUE!
4 OFF OFF #VALUE! #VALUE! #VALUe!





I wuld be most grateful if someone could give me a solution for this problem

Thank you

[pecoflyer]

Hi and welcome

please post a sample sheet ( no pics please). Thx

[Sonja]

Here's my sample.
Thanks

[Beamer]

Will the iserror function do the job?

in Cell D2: =IF(ISERROR(C2-B2+(B2>C2))*24,0,(C2-B2+(B2>C2))*24)

[Kevin UK]

Hi Sonja

See the attached, shorter formulas, if you had Excel 2007> you could shorten them using the IFERROR function.