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

 

[Guest]

=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