Difference between date and times
 

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

Difference between date and times
 

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
Hi Neil,

format your times to [hh]:ss

this takes care of times over 24 hours

oldchippy :)


--
oldchippy
------------------------------------------------------------------------
oldchippy's Profile: http://www.excelforum.com/member.php...o&userid=19907
View this thread: http://www.excelforum.com/showthread...hreadid=572163

Difference between date and times
 

=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