performing a calculation on a time range within 1 cell

  • Thread starter Thread starter AlanN
  • Start date Start date
A

AlanN

If in cell A1 the text "3:00-5:00" (3:00 am to 5:00 am) appears, how would one create a calculation in cell B1 to show the elapsed time between? I will have to have it work over a 24 hour clock in 15 minute increments. Examples:

23:00-2:15 = 3:15
15:00-16:30 = 1:15
11:15-15:30 = 4:15

In all cases there will be no spaces between characters and always a "-" (minus sign) separating the times.

Any ideas?

TIA (again!) , Alan
 
Hi Alan
the best way would be to separate the cell into two cells (starting
time and ending time). Iff that is possible you can use the formula
=B1-A1+(A1>B1)
to calculate the elapsed time

If this is not possible you have to seperate the time values:
1. Starting time: =TIMEVALUE(LEFT(A1,FIND("-",A1)-1))
2. Ending time: =TIMEVALUE(MID(A1,FIND("-",A1)+1,10))

To calculate the elapsed time use:
=TIMEVALUE(MID(A1,FIND("-",A1)+1,10))-TIMEVALUE(LEFT(A1,FIND("-",A1)-1)
)+(TIMEVALUE(LEFT(A1,FIND("-",A1)-1))>TIMEVALUE(MID(A1,FIND("-",A1)+1,1
0)))

as you can see, it would be better to separate the time information in
to cells

Frank
 
Wow... thanks, Frank. I have to have it in one cell unfortunately, so the
latter works best for me.

Alan
 
Hi again, Frank.
One more thing to add/enhance in this formula...
I have to convert the result achieved into hours, so if the cell contained
23:15-3:45, I need to display 4.5 (hours).
Can this be done?
Thanks again, Alan
 
Hi Alan
if you format the cell as 'Time' it will display hours (in your
example: 4:30). If you need a decimal just multiply the cell with 24
(and format this result as number)
In one formlua try:
=(TIMEVALUE(MID(A1,FIND("-",A1)+1,10))-TIMEVALUE(LEFT(A1,FIND("-",A1)-1
)
)+(TIMEVALUE(LEFT(A1,FIND("-",A1)-1))>TIMEVALUE(MID(A1,FIND("-",A1)+1,1
0))))*24

Frank
 
Back
Top