calculate the date & time

J

James_0159

Can Anyone help,
I want to be able to calculate easily the difference btwn two date and times,
eg:
start - 21-Jan-2004 07:30
finish - 22-Jan-2004 06:30
the answer I want to see is 23 hours.
If the times are less than 24 hrs I don't have an issue, but if the shutdown
goes over 24 hours I have some difficulties calculating. In reality if I only
enter the start and finish times I really want to see the actual durations.
eg:
Start - 07:30
Finish - 06:30
If the shut finishes on the next day at 06:30 it should calculate out at
23Hrs.
 
N

Nick Hodge

James

It very much depends on whether they will ever be more than 24 hours
different. If not and the data is Start Time A1, End Time A2 then in A3
enter

=IF(A2<A1,1-(A1-A2),A2-A1)

If it is more than 24 hours then you will need the date. In this case
(using same cells) it is just

=A2-A1

The trick here is to format the cells (Format>Cells...>Number>Custom)
[hh]:mm:ss

The square brackets stop XL from incrementing the hours greater than 24 into
another day, thereby only showing you the hours of that day. (eg if the
result was 25 hours, normally XL will show you a date (01/01/1900) and the
time 01:00:00)

For a better explanation of how Excel handles dates and times go to

http://www.cpearson.com/excel/datetime.htm

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]
 
Top