Difference between date and times

[Neil_Pattison]

I have a problem which I know can be sorted out easily but my mind has gone blank.

I have four columns showing dates and times.

Column A: Date On
Column B: Time On
Column C: Date Off
Column D: Time Off

Dates are in the format of dd/mm/yyyy and times are in the format of hh:mm.

What I'm wanting to do is calculate the difference in hours between the two times. The problem I'm having is when these times carry over a day.

...........A.............B..............C........D

i.e. 15/08/2006 17:10 16/08/2006 01:27


The actual time difference is 8 hrs and 16 mins; but the way I have it set up gives a negative value.

Any help with how to do this will be greatly appreciated

[oldchippy]

I have a problem which I know can be sorted out easily but my mind has gone blank.

I have four columns showing dates and times.

Column A: Date On
Column B: Time On
Column C: Date Off
Column D: Time Off

Dates are in the format of dd/mm/yyyy and times are in the format of hh:mm.

What I'm wanting to do is calculate the difference in hours between the two times. The problem I'm having is when these times carry over a day.

...........A.............B..................C...................D

i.e. 15/08/2006 17:10 16/08/2006 01:27


The actual time difference is 8 hrs and 16 mins; but the way I have it set up gives a negative value.

Any help with how to do this will be greatly appreciated

Hi Neil,

format your times to [hh]:ss

this takes care of times over 24 hours

oldchippy

[Sloth]

=D1+C1-B1-A1

This formula will work in your case, but it might be better to include the
dates and times in one cell instead.

Example
A B C
15/8/2006 17:10 16/8/2006 1:27 =B1-A1

format the first two like this...
mm/dd/yyyy h:mm
and format the last column like this
[h]:mm

"Neil_Pattison" wrote:

>
> I have a problem which I know can be sorted out easily but my mind has
> gone blank.
>
> I have four columns showing dates and times.
>
> Column A: Date On
> Column B: Time On
> Column C: Date Off
> Column D: Time Off
>
> Dates are in the format of dd/mm/yyyy and times are in the format of
> hh:mm.
>
> What I'm wanting to do is calculate the difference in hours between the
> two times. The problem I'm having is when these times carry over a day.
>
>
> ...........A.............B..............C............D
>
> i.e. 15/08/2006 17:10 16/08/2006 01:27
>
>
> The actual time difference is 8 hrs and 16 mins; but the way I have it
> set up gives a negative value.
>
> Any help with how to do this will be greatly appreciated
>
>
> --
> Neil_Pattison
> ------------------------------------------------------------------------
> Neil_Pattison's Profile: http://www.excelforum.com/member.php...o&userid=27696
> View this thread: http://www.excelforum.com/showthread...hreadid=572163
>
>