How do I calculate duration entered in HH:MM-HH:MM format
 

In Excel 2003, is it possible to calculate time duration when a single cell
has a time range of HH:MM-HH:MM (ex. 10:30-14:30)? I know its possible to do
so when comparing data from different cells, but I'm having trouble doing so
when both times are in a single cell - trying to pull report data from a file
export, having the times in 2 cells isn't an option.

How do I calculate duration entered in HH:MM-HH:MM format
 

hi
something like this might work
=RIGHT(E2,5)-LEFT(E2,5)
but each time would have to have 5 characters
9:30 - 9:45 would need to modify the formula to =RIGHT(E2,4)-LEFT(E2,4)
but 09:30 - 09:45 would work with the 5 character formula.

regards
FSt1

"JW73" wrote:

In Excel 2003, is it possible to calculate time duration when a single cell
has a time range of HH:MM-HH:MM (ex. 10:30-14:30)? I know its possible to do
so when comparing data from different cells, but I'm having trouble doing so
when both times are in a single cell - trying to pull report data from a file
export, having the times in 2 cells isn't an option.

How do I calculate duration entered in HH:MM-HH:MM format
 

Thanks - that worked, together with reformatting the destination cell with a
special format to only show hour - standard format was converting it to 0.37
or so for an 8.5 hour period, hh:mm or hh.mm was showing 8:30 or 8.30 - looks
like I'd have to add in a formula conversion to change any .3 to a .5 to get
it into a real decimel format - working on converting time sheet data into a
reporting matrix, just about have the details worked out now.

"FSt1" wrote:

hi
something like this might work
=RIGHT(E2,5)-LEFT(E2,5)
but each time would have to have 5 characters
9:30 - 9:45 would need to modify the formula to =RIGHT(E2,4)-LEFT(E2,4)
but 09:30 - 09:45 would work with the 5 character formula.

regards
FSt1

"JW73" wrote:

In Excel 2003, is it possible to calculate time duration when a single cell
has a time range of HH:MM-HH:MM (ex. 10:30-14:30)? I know its possible to do
so when comparing data from different cells, but I'm having trouble doing so
when both times are in a single cell - trying to pull report data from a file
export, having the times in 2 cells isn't an option.

How do I calculate duration entered in HH:MM-HH:MM format
 

If you want the answer in hours just multiply by 24, i.e.:

=(RIGHT(E2,5)-LEFT(E2,5))*24

Format the cell as number or general.

Hope this helps.

Pete

On Apr 30, 4:14*am, JW73 wrote:
Thanks - that worked, together with reformatting the destination cell with a
special format to only show hour - standard format was converting it to 0.37
or so for an 8.5 hour period, hh:mm or hh.mm was showing 8:30 or 8.30 - looks
like I'd have to add in a formula conversion to change any .3 to a .5 to get
it into a real decimel format - working on converting time sheet data into a
reporting matrix, just about have the details worked out now.



"FSt1" wrote:
hi
something like this might work
=RIGHT(E2,5)-LEFT(E2,5)
but each time would have to have 5 characters
9:30 - 9:45 would need to modify the formula to =RIGHT(E2,4)-LEFT(E2,4)
but 09:30 - 09:45 would work with the 5 character formula.

regards
FSt1

"JW73" wrote:

In Excel 2003, is it possible to calculate time duration when a single cell
has a time range of HH:MM-HH:MM (ex. 10:30-14:30)? I know its possible to do
so when comparing data from different cells, but I'm having trouble doing so
when both times are in a single cell - trying to pull report data from a file
export, having the times in 2 cells isn't an option.- Hide quoted text -

- Show quoted text -