Hi,

i am having deficulty to add up 2 time data.

I am copying this information from a web browser and pasting this onto
excel. The format for the date is the following: 11:30 02/01/2009

i then use the following formula to omit the date so only the time is shown.
=LEFT(Sheet1!E144,5). Which shows me the time as 11:30.

What I am then trying to do is take depart time away from arrive to give me
total journey time. I am using the following formula:
=IF(E133<D133,(E133+1)-D133,E133-D133). This work fine for all time before
midnight but I get an error for if one of the time is over midnight eg. 23:00
- 01:30. If I key in the time with using the LEFT formula then this works
without any problems.

Can someone help me please.

Thanks

Yogin

The LEFT (and RIGHT) functions extract text values, and you're wanting
values. Should be able to change your one formula to
=VALUE(LEFT(Sheet1!E144,5))
Note that you may need to format this cell to Time to get it to actually
display correctly.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Yogin" wrote:

Hi,

i am having deficulty to add up 2 time data.

I am copying this information from a web browser and pasting this onto
excel. The format for the date is the following: 11:30 02/01/2009

i then use the following formula to omit the date so only the time is shown.
=LEFT(Sheet1!E144,5). Which shows me the time as 11:30.

What I am then trying to do is take depart time away from arrive to give me
total journey time. I am using the following formula:
=IF(E133<D133,(E133+1)-D133,E133-D133). This work fine for all time before
midnight but I get an error for if one of the time is over midnight eg. 23:00
- 01:30. If I key in the time with using the LEFT formula then this works
without any problems.

Can someone help me please.

Thanks

Yogin

Assuming :

D133 contains the text: "11:30 02/01/2009"
E133 contains the text: "01:30 03/01/2009"

and the date convention is dd/mm/yyyy

then if

F133 contains the formulae: =VALUE(RIGHT(E133,10)&"
"&LEFT(E133,5))-VALUE(RIGHT(D133,10)&" "&LEFT(D133,5))

the result will be 14:00:00 hours - when formatted in Time format or
0.58333333 in decimal notation.

if you get the date style incorrect then that could also end up being
27.583333 (27 days)+ 14 hrs
ie. it's really being interpreted as "mm/dd/yyyy" rather than 'dd/mm/yyyy"






"Luke M" wrote in message
...
The LEFT (and RIGHT) functions extract text values, and you're wanting
values. Should be able to change your one formula to
=VALUE(LEFT(Sheet1!E144,5))
Note that you may need to format this cell to Time to get it to actually
display correctly.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Yogin" wrote:

Hi,

i am having deficulty to add up 2 time data.

I am copying this information from a web browser and pasting this onto
excel. The format for the date is the following: 11:30 02/01/2009

i then use the following formula to omit the date so only the time is
shown.
=LEFT(Sheet1!E144,5). Which shows me the time as 11:30.

What I am then trying to do is take depart time away from arrive to give
me
total journey time. I am using the following formula:
=IF(E133<D133,(E133+1)-D133,E133-D133). This work fine for all time
before
midnight but I get an error for if one of the time is over midnight eg.
23:00
- 01:30. If I key in the time with using the LEFT formula then this works
without any problems.

Can someone help me please.

Thanks

Yogin