Calculate hours from 10:00-21:00 format

[Jorch]

Hello!

mr Somebody has a several worksheets wich includes lots of cells which
has start and end hours in format 10:00-18:00, 13:15-21:15 etc...

Cells are formatted as text.

He asked me if it's possible to calculate hours between start and
end hours automaticly. I check out macros and notice that java, php
and few other programming languages does not help me now.

I notice that if end hour is before start hour calculation works but
not vice versa.

How can i split string into two pieces and calculate it to other cell.

Let's say cell X includes string "10:00-20:00", I split it to Array
whics becomes 2 dimension arr and then calc cell Y value =
Array2-Array1.

Is there function to evaluate text cells???

I think this is possible but how????????

Advanced Super Thanx
-jori luoto

 

[Frank Kabel]

Hi
why not split these values in two columns. Use 'Data - Text to columns'
for this. Afterwards simply subtract the values with a formula like
=B1-A1

and if your time values can span midnight use
=B1-A1+(B1<A1)

 

[Bob Phillips]

Hi Frank,

a better formula (IMO) is

=MOD(B1-A1,1)

One for your library?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

Bob
neat

 

[Jorch]

Thanx both of you... that seems to be working fine.
That was kind of thing i was searching.

-jori