Time formula

[Yogin]

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

 

[Luke M]

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.

 

[Steven]

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"