"msbutton27" wrote:
it gives me the #VALUE!

I wrote in my response:
And that assumes that when the OP wrote Jul 19 2009 08:30,
for example, he meant that the cell contains a true date serial
number, perhaps entered as Jul 19, 2009 8:30 AM, which has
the custom format "mmm dd yyyy hh:mm" (without quotes).
Or does the cell contain text?

I think your result confirms my suspicion: you have Jul 19 2009 08:30 as
text.


Any other suggestions on what I might be doing wrong.

Ideally, you should enter your date/time data in a form that Excel
recognizes and converts into a date serial number. For example, enter Jul
19, 2009 08:30 (note the comma). You can use the custom format "mmm dd
yyyy hh:mm" (without quotes) to display the date without the comma, if you
wish.

Alternatively, you can convert the text to a numeric value. How you do that
depends on how the date Jul 1 would appear: Jul 01, Jul 1 (two spaces
before 1), or Jul 1 (one space before 1).

If either of the first two cases, then:

=(LEFT(B1,6)&","&RIGHT(B1,11)) -
(LEFT(A1,6)&","&RIGHT(A1,11))

formatted with the custom format [h]:mm.

If the latter (Jul 1, one space before 1), then:

=(LEFT(B1,LEN(B1)-11)&","&RIGHT(B1,11)) -
(LEFT(A1,LEN(A1)-11)&","&RIGHT(A1,11))


----- original message -----

"msbutton27" wrote in message
...
Thanks for the reply, but the equation below fails. I setup Column A,B
and C
as time you suggested below and it gives me the #VALUE! sign :(

Any other suggestions on what I might be doing wrong.

"Mike H" wrote:

Hi,

=B1-A1

format as [hh]:mm

Mike

"msbutton27" wrote:

Hi folks, I have hopefully what will be a quick and easy question.

I have two colums (A & B) that hold two date and time values, a start
and
complete time.
Jul 19 2009 08:30 Jul 19 2009 12:54

I want to be able to find out the actual time difference, for example
above
would show 4:24 hours.

In some cases the start and complete dates could be a day or 2 apart,
therefore I would want the output to be displayed in hour format, for
example:

Jul 17 2009 08:30 Jul 19 2009 12:54

Column C should be 52:24 hours.

Is there a way to do this.