wrote:
Has anyone ever seen time entered as '7:00 - 9:00' in a
TIME formatted cell? (It does allow the entry)

To explain.... The form "7:00 - 9:00" is interpreted as text. We can
always enter text into a cell, regardless of the numeric format (like Time).


"Claus Busch" wrote:
if you may not separate these values try it with formula:
=MOD(MID(A1,FIND("- ",A1)+2,99)-LEFT(A1,FIND(" ",A1)-1),1)
The MOD function works with all times but especially with
midnight rollovers

Clever! But beware of inherent arithmetic inaccuracies due to the way that
numbers are represented internally (64-bit binary floating-point).

For example, for "6:15 - 7:15" in A1, the MOD result in B1 is displayed as
1:00, but =B1=TIME(1,0,0) returns FALSE(!).

A more-reliable formula would be:

=--TEXT(MOD(MID(A1,FIND("- ",A1)+2,99)-LEFT(A1,FIND(" ",A1)-1),1), "hh:mm")

PS: The unreliability is not limited to using MOD(...,1). The problem
would arise even if we entered 6:15 and 7:15 into separate cells (A1, B1)
and calculated =B1-A1.