Adding hours from times in one cell

[Patches01]

Hi All,
In need of some help. I am trying to find the hours from times entered into one cell.
I have this so far
=IF(Sheet1!E50="N/A","",IF(Sheet1!E50<>"",(TRIM(REPLACE(Sheet1!E50,1,FIND("-",Sheet1!E50),""))-TRIM(LEFT(Sheet1!E50,FIND("-",Sheet1!E50)-1)))+TRIM(RIGHT(Sheet1!E50,FIND("-",Sheet1!E50)-1))*24,""))
However when the times for example 21:00 - 5:30, which is 21:00 for starting time of a shift & 5:30am for the end time of the shift, it gives me a total hours of 4.854, which is in correct as it is 8.5 hours

Jess

21:00 - 05:30

21:00 - 05:30

21:00 - 05:30

21:00 - 05:30

Where am I going wrong.
It works for times as follows

Cathy

6:00 - 15:00

6:00 - 15:00

6:00 - 15:00

6:00 - 15:00

6:00 - 15:00

 

[Alex Blakenburg]

Does everyone who is using this have MS 365 ? If they do you can use this:

=LET(tStart,TIMEVALUE(TEXTBEFORE(E50,"-")),
          tEnd,TIMEVALUE(TEXTAFTER(E50,"-")),
          IF(tEnd<tStart,tEnd+1-tStart,tEnd-tStart)*24)

 

[Dave Patton]

Let_a.xlsm
	E	F	G
4			With 365
5	21:00 - 05:30	8.5	8.5
6	06:00 - 15:00	9.0	9.0
7	6:00 - 15:00	9.0	9.0
8
4d

Cell Formulas
Range	Formula
F5:F7	F5	=(RIGHT(E5,5)-LEFT(E5,5)+(--LEFT(E5,5)>--RIGHT(E5,5)))*24
G5:G7	G5	=LET(e,RIGHT(E5,5)+0,s,LEFT(E5,5)+0,(e-s+(s>e))*24)