# Mid() Range #Value

Mar 7, 2008I have a column A1:A5 which holds text format:

15:00 - 18:30

07:30 - 21:00

13:00 - 14:00

19:30 - 20:30

20:30 - 23:30

=SUMPRODUCT(MID(A1:A5;9;2)+0+MID(A1:A5;12;2)/60-MID(A1:A5;1;2)+0-MID(A1:A5;4;2)/60)

I use the above formula to calculate the total hours in the 5 timespans (in this case 22 hours). It can probably be shortended but remember im just learning this.

It works, but my problem is that once in a while one of the cells A1:A5 will be empty, or hold a text string with no numbers. When this happens my formula returns #Value!.